Wait for called program to finish

U

Uncle Albert

I have a macro in a spreadsheet that receives a userid and password, then
calls a cmd file to start an app to generate spreadsheet reports from data in
an Oracle database. I find that the macro does not wait for the cmd file to
finish executing before continuing to the following steps. I have put in a
timing loop to stall (hopefully) long enough for the code to finish, but I
would assume that there is a more elegant solution.

How can I get the macro to wait for the cmd file to finish its work before I
start trying to use the resulting spreadsheet reports that it generates?

If it helps, here is the section of code that I am using.

' Log onto Discoverer, run the workbooks and export the results to Excel
ChDir ("P:\Reports\RDS\Admissions\SpreadsheetRawData\")
Shell "P:\Reports\RDS\Admissions\SpreadsheetRawData\_DataGetter.cmd " &
logonstring, vbNormalFocus
'
' Give Discoverer time to run
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 12
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
'
' Open the Excel raw data sheets to update the data in the master report
Sheets("Raw Data Sheet").Select
ActiveWindow.SmallScroll Down:=-27
Range("A29").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Windows("_ADMU Funnel Reports - Enrollment Report .xls").Activate
and so forth...
 
G

Guest

Hmmm, how about getting the database to trigger some value in some table
that indicates completion. Then use a Do...Loop in VBA to poll that until
it's complete.
Or poll the modified data to see if there's relevant data, say today's date,
before completing.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top