Wait for called program to finish
 

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\_Dat aGetter.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...

Wait for called program to finish
 

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.

"Uncle Albert" <Uncle wrote in message
...
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\_Dat aGetter.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...