Wait For File To Exist Function

Hello,

I'm working with a template file that pulls data from SAP then runs a formatting macro. I'm currently using a simple sleep timer while building the functionality of the macro. The purpose of the sleep timer is to pause the VBA code while a VBS script runs to pull data from SAP. The problem is the deviation in the time that it takes SAP to pull some data compared to pulling other data, making a sleep timer not so optimal. So I'm looking for any ideas to do a check to see if the VBS script is done pulling.

SAP exports the data into multiple excel files on my desktop, always in the same order, So I would assume that the easiest way to check if the VBS script is done would be to check if the last excel file exist yet on my desktop. I'm just not sure how to go about doing that.

Also consider, to conserve CPU usage, I would like to preform the check every 2-3 seconds.

I would also like to implement a timeout feature, so that the code doesn't get stuck in an infinite loop.

Once the file is found, I want the rest of my VBA code to run.

Any ideas or sample code would be appreciated.

http://www.ozgrid.com/Excel/run-macro-on-time.htm

Hi BryceVBA,

There are several methods that can be used to do what you want including:
a. API (Application Programming Interfaces) Sleep routine (my first choice)
b. Application.OnTime
c. Application.Wait
d. API Timer routines

I prefer the Sleep routine because it is:
a. relatively simple to use and plays nicely with others
b. allows access to Excel while waiting (not needed in this application)

In cases where I need access to Excel, I usually set the repeat rate at 200 or 250 Milliseconds (about 0.2 or 0.25 seconds).

See the attached file which contains the following code that has the capability of:
a. Creating Scratch file zyx.xlsx in the same folder as the file running the code.
b. Deleting file zyx.xlsx
c. Waiting for file zyx.xlsx to exist or timeout

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

Thanks a lot! It's working now