Loop through folder and run Script on each file found - VisualCron - Forum

Community forum

CamskillIT
2014-04-23T09:45:56Z
Hi

I am trying to setup a job involving a folder full of Excel sheets. These Excel sheets run a piece of stored VBA everytime they are loaded and files are constantly renamed or created by users on a daily basis. I was looking to setup, in VisualCron, a task of "List Files" pointing to this folder, which would then loop through to the task below which would load each Excel sheet in question, let the VBA run and then close the Excel file, then continue onto the next Excel file in the folder until all these had completed and then end the job. I have had a look at the Loop function but I really just cant seem to get it working and I was hoping someone would have an idea?

I realise I haven't written this very clearly but please let me know if you require any more information as I am sure I'm just missing something obvious in VisualCron.

Also thanks for creating VisualCron, you have saved me weeks of time over the last few years with it and I have recommended it to many other people in my industry.

Cheers!


Update - I have managed, with the help of your videos which I didnt notice were available sorry, to setup this loop but when I am passing the variable through it is actually just passing through "[Only available at runtime]" Instead of the actual variable? I cant see anything I did differently than the tutorial so I am really struggling to see the issue here. We are running version 6.1.9 of VisualCron. I hope you can help me with this.

Thanks again
Support
2014-04-23T13:18:11Z
Where exactly do you get this [Only available at runtime]. Could you attach a screenshot?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
CamskillIT
2014-04-23T13:31:04Z
Thanks for your response. Actually on the server we are running the scripts on, it is trying to load Excel with the variable I have passed through to it. I have attached some screenshots of how the job is setup.

CamskillIT attached the following image(s):
Support
2014-04-23T13:51:44Z
Thanks, we will test this and get back to you.
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
CamskillIT
2014-04-23T13:53:56Z
Thanks! I look forward to hearing back from you.
bbusse
2014-04-23T16:07:16Z
I can confirm, the Arguments field on the Execute task does not appear to be capable of using Loop variables. I'm running the latest 7.1.6 build and did this with the same result. Only I did the following command so it'd write out the file names to a text file (Excel had an issue too, but I didn't want to deal with closing excel multiple times).

Command: c:\windows\system32\cmd.exe
Arguments:  /c echo {LOOP(CurrentValueX)} >>d:\temp\visualcron\CamskillIT\output.txt


I have 2 files in the folder i'm doing the 'list files' task on... and the output.txt file generated just contains this:
[Only available at runtime]
[Only available at runtime]


Brian
Support
2014-04-23T18:56:30Z
Have you changed the Execution context so that you run this Task in Foreground mode?
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
bbusse
2014-04-23T19:30:36Z
Originally Posted by: Support 

Have you changed the Execution context so that you run this Task in Foreground mode?



i have, yes. I did that when i was launching Excel during my first test so i could just click the 'X' on it to close before launching the 2nd one.

AHH! That was it. if it's background execution, it works fine. It breaks (like noted before) when in foreground execution (at least on my 7.1.6 install)

I changed it to background execution (first time running this test that way) and my file now contains this instead, so the variable works:

D:\temp\VisualCron\CamskillIT\All_Desktop_OS_Systems.xls
D:\temp\VisualCron\CamskillIT\Duplicate_Groups.xls


Brian
Support
2014-04-23T20:25:03Z
Thanks for the report. The issue should have been fixed in this version:

http://www.visualcron.co....aspx?g=posts&t=4059 
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
bbusse
2014-04-23T21:02:58Z
Originally Posted by: Support 

Thanks for the report. The issue should have been fixed in this version:

http://www.visualcron.co....aspx?g=posts&t=4059 



I just validated using my output to text file and also launching Excel the way the OP was doing and was successful in both Foreground and Background execution. Hopefully he will have the same results when able to test.

Brian
CamskillIT
2014-04-23T21:26:24Z
I did have it set to foreground execution yes, I will test this first thing when I get back to work tomorrow and update. Thanks for the quick responses!
CamskillIT
2014-04-24T10:50:54Z
Just wanted to confirm that this has fixed the issue I was having. Thanks for the great support and quick response!

Cheers

Iain
Support
2014-04-24T11:18:28Z
Great - thanks for the feedback!
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Scroll to Top