Wait For File To Exist Function

Hello,

My VBA macro runs a VBscript to pull a report from SAP. I need to pause the vba script to account for the time it takes the VBscript to fully run. I was using a function that waits for the file to be saved to my desktop, but this SAP report can't be saved and exported. It can only be exported to excel. So I was thinking of another way of checking to see if the VBscript is done running. That is looping an activate wb function. If the wb activates, then continue the vba script. If the wb doesn't activate, then pause for a couple of seconds and try to activate the wb again.

The code below waits for the file to exist on my desktop. I'm hoping someone can assist me in editing the code to loop activating the wb instead of checking for the file on my desktop, or point me in the right direction as to what needs edited.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

you probably want to loop through the workbook collection each time

Please Login or Register  to view this content.

Although wouldn't it be tidier to change the VBscript to export it to a known location?
And better yet to do the whole thing either in VBA or the VBscript?
Or if you have the sap data warehouse (BW/AFO) to use that?

hello and thanks for the response.

I've heard of translating VBscript to VBA before, but I have no knowledge of how to do that. Also I'm not technically skilled enough to implement the activate wb check.

But I did think of a way around this obstacle. Instead of just pulling the one report that can't be saved to my desktop, I'm going to change my VBscript to pull that report first followed by a very short report that can be saved to my desktop from SAP.

Just using the current check I have now will register when I have the second report on my desktop and then proceed with the VBA macro. In a sense, if I have the second report, then I have the first report. Then I'll just kill the file later in the VBA macro.

This should take an extra 15-30 seconds, but it's the only implementable check I can think of for now.

I'll leave this thread listed as unsolved incase someone else can think of a more efficient way of doing this.

Using a phone so not going to read the code above; or add code to this post, but if i was to wait for a file to exist (and be available for the app), I'd write a little procedure to first check the file exists, then try to open it for input with an exclusive lock. If it can be locked then no other process has the file open.

That can all be bound up in a loop which will exit if a set time has elapsed... Just a thought, but I'll have a look at the logic of the code you posted a little later, if you want.

Originally Posted by cytop

I'll have a look at the logic of the code you posted a little later, if you want.

Sure, thanks for taking a look!