Ensuring Excel start macros complete before terminating
 

I have a VBS script that runs each night. It is supposed to open an
Excel document so that it's start macros run and do some preprocessing.
It appears that I am terminating the application before the macros get
a chance to run. Is there a flag I can check to see if they are still
processing before I exit the application?

Thank you

Here is the code I am using. It is quite short, most of the code is for
troubleshooting purposes...

========================
On Error Resume Next

Set oExcel = CreateObject("Excel.application")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTS = oFSO.OpenTextFile("autodialer_cube.log", 2, True)

oTS.WriteLine Now & " Opening workbook..."

oExcel.Workbooks.Open "\\CASK\groups\AutoDialer\AutoDialer.xls"
oExcel.ActiveWorkbook.RunAutoMacros 1
oExcel.ActiveWorkbook.Saved = True

oTS.WriteLine oExcel.ActiveWorkbook.Worksheets(1).Cells(5, 1).Value

If Err.Number < 0 Then
oTS.WriteLine Now & " (" & Err.Number & ") " & Err.Description
Err.Clear
End If

oTS.WriteLine Now & " Closing workbook..."

oExcel.Quit

If Err.Number < 0 Then
oTS.WriteLine Now & " (" & Err.Number & ") " & Err.Description
Err.Clear
End If
========================

Ensuring Excel start macros complete before terminating
 

I don't see anything in your macro that wouldn't be synchronous, so I would
expect the process to finish for each step.

I guess it could depend on what is in the Auto_Open macro of the workbook.
If it is doing a background query, then this can be problematic, or using
Application.OnTime.

--
Regards,
Tom Ogilvy


"karlman" wrote in message
oups.com...
I have a VBS script that runs each night. It is supposed to open an
Excel document so that it's start macros run and do some preprocessing.
It appears that I am terminating the application before the macros get
a chance to run. Is there a flag I can check to see if they are still
processing before I exit the application?

Thank you

Here is the code I am using. It is quite short, most of the code is for
troubleshooting purposes...

========================
On Error Resume Next

Set oExcel = CreateObject("Excel.application")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTS = oFSO.OpenTextFile("autodialer_cube.log", 2, True)

oTS.WriteLine Now & " Opening workbook..."

oExcel.Workbooks.Open "\\CASK\groups\AutoDialer\AutoDialer.xls"
oExcel.ActiveWorkbook.RunAutoMacros 1
oExcel.ActiveWorkbook.Saved = True

oTS.WriteLine oExcel.ActiveWorkbook.Worksheets(1).Cells(5, 1).Value

If Err.Number < 0 Then
oTS.WriteLine Now & " (" & Err.Number & ") " & Err.Description
Err.Clear
End If

oTS.WriteLine Now & " Closing workbook..."

oExcel.Quit

If Err.Number < 0 Then
oTS.WriteLine Now & " (" & Err.Number & ") " & Err.Description
Err.Clear
End If
========================

Ensuring Excel start macros complete before terminating
 

Would it help to set a doc property to some value at the end of the
Auto_Open macro, and loop a read code in the calling code that will not
terminate until the value is set?

Ed

"Tom Ogilvy" wrote in message
...
I don't see anything in your macro that wouldn't be synchronous, so I
would
expect the process to finish for each step.

I guess it could depend on what is in the Auto_Open macro of the workbook.
If it is doing a background query, then this can be problematic, or using
Application.OnTime.

--
Regards,
Tom Ogilvy


"karlman" wrote in message
oups.com...
I have a VBS script that runs each night. It is supposed to open an
Excel document so that it's start macros run and do some preprocessing.
It appears that I am terminating the application before the macros get
a chance to run. Is there a flag I can check to see if they are still
processing before I exit the application?

Thank you

Here is the code I am using. It is quite short, most of the code is for
troubleshooting purposes...

========================
On Error Resume Next

Set oExcel = CreateObject("Excel.application")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oTS = oFSO.OpenTextFile("autodialer_cube.log", 2, True)

oTS.WriteLine Now & " Opening workbook..."

oExcel.Workbooks.Open "\\CASK\groups\AutoDialer\AutoDialer.xls"
oExcel.ActiveWorkbook.RunAutoMacros 1
oExcel.ActiveWorkbook.Saved = True

oTS.WriteLine oExcel.ActiveWorkbook.Worksheets(1).Cells(5, 1).Value

If Err.Number < 0 Then
oTS.WriteLine Now & " (" & Err.Number & ") " & Err.Description
Err.Clear
End If

oTS.WriteLine Now & " Closing workbook..."

oExcel.Quit

If Err.Number < 0 Then
oTS.WriteLine Now & " (" & Err.Number & ") " & Err.Description
Err.Clear
End If
========================

Ensuring Excel start macros complete before terminating
 

Interesting idea! I will give it a try... Thanks