vTask Forum - Get File names in specific folder and paste into Excel cell

Yman

Full Member (10+)

Offline

Posts: 16

Get File names in specific folder and paste into Excel cell
Feb 26^th, 2010 at 6:30am

Hi,

i am trying to do the following:

1. Point on a folder
2. Retrieve the file names of all files in the folder
3. Point on an excel cell
4. Paste the file names into that cell in such a way that "/" appears before the file name and "," after it (example: /file1.xls,/file2.xls,/file3.xls)

I am managing to do the steps 1 and 3 (Select Folder, Select Cell) but i do not get the steps 2 and 4 done.

Any help is very appreciated!

Back to top

IP Logged

Thomas

vTask Sultan (500+)

Offline

Using vTask for over a
Fiftieth of a Century...

Posts: 553
Kirkland, WA

Re: Get File names in specific folder and paste into Excel cell
Reply #1 - Feb 26^th, 2010 at 10:03am

I am not sure about "Point on a Cell".
If you mean it literally then run the script, select the folder, the script will end. Right click on the cell and paste.

If you mean "Select a Cell Programmatically" remove the "Set Clipboard Text" action and the END action and the script will put the value in B1 of DemoDocument.xls on your desktop.

I hope this helps to get you started.

VXM Script: (Copy and Paste into vTask)

<step>
	<action>Set Variable</action>
	<output value="%Files">Variable</output>
	<comment>Create empty list</comment>
</step>
<step>
	<action>Select Folder</action>
	<value1>Select Folder</value1>
	<output value="%FolderPath">Variable</output>
	<comment>Select folder</comment>
</step>
<step>
	<action>Start Files Loop</action>
	<text>%FolderPath\*.*</text>
	<output value="%FileName">Variable</output>
	<comment>Loop though *.* files in folder</comment>
</step>
<step>
	<action>Comment</action>
	<comment>The example did not use the full path that is returned by File Loop so the next lines get just the name.</comment>
</step>
<step>
	<action>Set Variable</action>
	<text>=COUNTIF("%FileName", "\")</text>
	<output value="%Cnt">Variable</output>
</step>
<step>
	<action>Set Variable</action>
	<text>=CHOOSE(%CNT+1,"%FileName", "\")</text>
	<output value="%FileName">Variable</output>
</step>
<step>
	<action>Set Variable</action>
	<text>%Files/%FileName,</text>
	<output value="%Files">Variable</output>
	<comment>%Files = Current List of files + "/" + new file name +","</comment>
</step>
<step>
	<action>NEXT LOOP</action>
</step>
<step>
	<action>Set Clipboard Text</action>
	<text>%files</text>
</step>
<step>
	<action>Exit Run</action>
</step>
<step>
	<action>Comment</action>
	<comment>Right click on the cell and paste, or remove the end and let the following lines do it automatically</comment>
</step>
<step>
	<action>Create Workbook</action>
	<text>width=200 align=center bgcolor=silver color=blue Sample Text</text>
	<value1>{desktop}\DemoDocument.xls</value1>
	<value2>,</value2>
</step>
<step>
	<action>Open Workbook</action>
	<value1>{desktop}\DemoDocument.xls</value1>
</step>
<step>
	<action>Set Cell</action>
	<text>%Files</text>
	<value1>{desktop}\DemoDocument.xls</value1>
	<value2>B2</value2>
</step>

Back to top

IP Logged

Yman Full Member (10+) Offline Posts: 16	Re: Get File names in specific folder and paste into Excel cell Reply #2 - Feb 27^th, 2010 at 10:48am It worked! Thanks a lot Thomas!
Back to top	IP Logged

Thomas vTask Sultan (500+) Offline Using vTask for over a Fiftieth of a Century... Posts: 553 Kirkland, WA	Re: Get File names in specific folder and paste into Excel cell Reply #3 - Feb 27^th, 2010 at 10:51am Glad to help.
Back to top	IP Logged