Jump to content
Macro Express Forums

Macro not waiting for Launch Program to complete?


Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...