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...
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...