Macro not waiting for Launch Program to complete?

[nuffcedd]

My macro launches a program (Bat. file) that is taking anywhere from 1 to 5 minutes to run. That program finishes by overriding an existing excel file with new data in a folder (report.xls). Then the macro places today’s date in a variable. Then the macro is SUPPOSE to copy the excel file (that has just been overridden) in the same folder with the date variable (report.date.xls).

 

After macro runs, there should be two excel spreadsheets (report.xls and report.date.xls) with same data.

 

The end result I am getting is the file that is overridden each day, (report.xls) is correct but (report.date.xls) is always the data before the override.

 

My best guess is the bat. File is still running and my macro is proceeding to the next step. If that is the case, is there a way to keep the macro from proceeding until the Launch program step is complete? I could use the Delay command but the amount of data I am dealing with is constantly increasing, and what is 5 minutes today may be 10 minutes in 6 months.

 

Any suggestions would be greatly appreciated.

[rberq]

I think very likely you have diagnosed the problem correctly. ME only launches the .bat file and does not wait for its completion.

 

Here's a commonly used ME trick. Select a control file name, like c:\temp\control.txt. The first thing your .bat file will do is delete that file. The last thing your .bat file will do is create that file, simply by copying a dummy file to that name.

 

Now, your macro will

1) start the .bat job

2) delay a few seconds just to make sure the .bat job has time to delete the control file

3) run a repeat loop checking for existence of the control file -- you probably want a few seconds delay in each pass through the loop just to reduce CPU time

4) when the control file is found to exist, you know your .bat job is done, so you can do the date stuff and copy the spreadsheet

[nuffcedd]

BINGO!

 

Thanks!

[randallf]

Have you tried 'wait for file to be ready' on the batch file? It might be willing to wait until the batch file closes and then take action. The file shouldn't be 'ready' until no program has a hook on it (like wscript or whatever runs them now).