Wait for an Excel Macro to Finish Before Continuing

[Jim Young]

I've looked around these news groups and, since I am a complete noob
in VBscripting, most of it was way over my head. So here's my
question in a nutshell.

I tested the macro being referenced in the below script within Excel
itself and it works fine. However, when I call the macro using the VB
script below, it doesn't wait for the macro to complete (it takes
about 5 minutes as the macro is pulling from an Essbase cube) - it
just plows forward through the rest of the macro (which, after pulling
the information, saves the workbook) and then through the rest of the
VB script

Is there a way to force the VBscript to wait while the Excel macro
finishes on its own?

Set exapp = CreateObject("Excel.Application")
exapp.visible = true
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings
exapp.Application.AskToUpdateLinks = false
exapp.workbooks.Open ("C:\Documents and Settings\Jim\Desktop\2011
Rack Daily-MTD-YTD Sales.xls")
exapp.run "DoAll" (FYI THIS MACRO IS ACTALLY CALLING SEVERAL MACROS
- AND IT WORKS FINE IN EXCEL)
' exapp.activeworkbook.save
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings
exapp.activeworkbook.close true
exapp.quit
set exapp = nothing

[Todd Vargo]

Post by Jim Young
I've looked around these news groups and, since I am a complete noob
in VBscripting, most of it was way over my head. So here's my
question in a nutshell.
I tested the macro being referenced in the below script within Excel
itself and it works fine. However, when I call the macro using the VB
script below, it doesn't wait for the macro to complete (it takes
about 5 minutes as the macro is pulling from an Essbase cube) - it
just plows forward through the rest of the macro (which, after pulling
the information, saves the workbook) and then through the rest of the
VB script
Is there a way to force the VBscript to wait while the Excel macro
finishes on its own?
Set exapp = CreateObject("Excel.Application")
exapp.visible = true
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings
exapp.Application.AskToUpdateLinks = false
exapp.workbooks.Open ("C:\Documents and Settings\Jim\Desktop\2011
Rack Daily-MTD-YTD Sales.xls")
exapp.run "DoAll" (FYI THIS MACRO IS ACTALLY CALLING SEVERAL MACROS
- AND IT WORKS FINE IN EXCEL)
' exapp.activeworkbook.save
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings
exapp.activeworkbook.close true
exapp.quit
set exapp = nothing

There is something wrong with your macro code that you are not showing here.

Most likely cause: Do any of your macros use a Shell function?

[Todd Vargo]

Post by Todd Vargo

Post by Jim Young
I've looked around these news groups and, since I am a complete noob
in VBscripting, most of it was way over my head. So here's my
question in a nutshell.
I tested the macro being referenced in the below script within Excel
itself and it works fine. However, when I call the macro using the VB
script below, it doesn't wait for the macro to complete (it takes
about 5 minutes as the macro is pulling from an Essbase cube) - it
just plows forward through the rest of the macro (which, after pulling
the information, saves the workbook) and then through the rest of the
VB script
Is there a way to force the VBscript to wait while the Excel macro
finishes on its own?
Set exapp = CreateObject("Excel.Application")
exapp.visible = true
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings
exapp.Application.AskToUpdateLinks = false
exapp.workbooks.Open ("C:\Documents and Settings\Jim\Desktop\2011
Rack Daily-MTD-YTD Sales.xls")
exapp.run "DoAll" (FYI THIS MACRO IS ACTALLY CALLING SEVERAL MACROS
- AND IT WORKS FINE IN EXCEL)
' exapp.activeworkbook.save
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings
exapp.activeworkbook.close true
exapp.quit
set exapp = nothing

There is something wrong with your macro code that you are not showing here.
Most likely cause: Do any of your macros use a Shell function?- Hide
quoted text -
- Show quoted text -

' Copyright 1992-1999 Hyperion Solutions Corporation. All Rights
Reserved.
' Use, duplication, or disclosure by the Government is subject to
' restrictions as set forth in subparagraph (c)(1)(ii) of the Rights
' in Technical Data and Computer Software clause at DFARS
252.227-7013,
' or in the Commercial Computer Software Restricted Rights clause at
' FAR 52.227-19, as applicable.
' Hyperion Solutions Corporation
' 1344 Crossman Avenue, Sunnyvale, CA 94089 USA
'
'
' /*********************************************************\
' * *
' * ESSXLVBA.TXT - Essbase Excel VBA Include File. *
' * *
' * For Essbase Release 6 *
' * *
' \*********************************************************/
'
' The following prototypes declare the Visual Basic menu-equivalent
functions

<snip>

Sub DoAll()
GetDate
ConnTY
RetData
DisConn

Try adding a msgbox here to indicate when subs are complete.

MsgBox "DoAll complete"

End Sub

Also try changing the VBScript to allow Excel to display any error messages.

Set exapp = CreateObject("Excel.Application")
exapp.visible = true
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings

If there are any errors in the VBS code, the line above is preventing you
from seeing them. So remove or comment it.



exapp.Application.AskToUpdateLinks = false
exapp.workbooks.Open ("C:\Documents and Settings\Jim\Desktop\2011
Rack Daily-MTD-YTD Sales.xls")
exapp.run "DoAll" (FYI THIS MACRO IS ACTALLY CALLING SEVERAL MACROS
- AND IT WORKS FINE IN EXCEL)
' exapp.activeworkbook.save
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings

Remove of comment this one above too.


exapp.activeworkbook.close true
exapp.quit
set exapp = nothing

Add another msgbox to end of VBScript to indicate when it finishes.

MsgBox "VBScript complete.


Sorry I don't know anything about ESSEXCLN.XLL so I can not offer any help
with prototype API calls.

[Jim Young]

Post by Todd Vargo

Post by Jim Young
I've looked around these news groups and, since I am a complete noob
in VBscripting, most of it was way over my head. So here's my
question in a nutshell.
I tested themacrobeing referenced in the below script within Excel
itself and it works fine. However, when I call themacrousing the VB
script below, it doesn'twaitfor themacroto complete (it takes
about 5 minutes as themacrois pulling from an Essbase cube) - it
just plows forward through the rest of themacro(which, after pulling
the information, saves the workbook) and then through the rest of the
VB script
Is there a way to force the VBscript towaitwhile the Excelmacro
finishes on its own?
Set exapp = CreateObject("Excel.Application")
exapp.visible = true
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings
exapp.Application.AskToUpdateLinks = false
exapp.workbooks.Open ("C:\Documents and Settings\Jim\Desktop\2011
Rack Daily-MTD-YTD Sales.xls")
exapp.run"DoAll" (FYI THISMACROIS ACTALLY CALLING SEVERAL MACROS
- AND IT WORKS FINE IN EXCEL)
' exapp.activeworkbook.save
exapp.Application.DisplayAlerts = False 'eliminates any alerts or
warnings
exapp.activeworkbook.close true
exapp.quit
set exapp = nothing

There is something wrong with yourmacrocode that you are not showing
here.
Most likely cause: Do any of your macros use a Shell function?- Hide
quoted text -
- Show quoted text -

' Copyright 1992-1999 Hyperion Solutions Corporation. All Rights
Reserved.
' Use, duplication, or disclosure by the Government is subject to
' restrictions as set forth in subparagraph (c)(1)(ii) of the Rights
' in Technical Data and Computer Software clause at DFARS
252.227-7013,
' or in the Commercial Computer Software Restricted Rights clause at
' FAR 52.227-19, as applicable.
' Hyperion Solutions Corporation
' 1344 Crossman Avenue, Sunnyvale, CA  94089  USA
'
'
'     /*********************************************************\
'     *                                                         *
'     *   ESSXLVBA.TXT - Essbase Excel VBA Include File.        *
'     *                                                         *
'     *   For Essbase Release 6                                 *
'     *                                                         *
'     \*********************************************************/
'
' The following prototypes declare the Visual Basic menu-equivalent
functions

<snip>

Sub DoAll()
   GetDate
   ConnTY
   RetData
   DisConn

Try adding a msgbox here to indicate when subs are complete.
MsgBox "DoAll complete"

End Sub

Also try changing the VBScript to allow Excel to display any error messages.
 Set exapp = CreateObject("Excel.Application")
 exapp.visible = true
 exapp.Application.DisplayAlerts = False  'eliminates any alerts or
warnings
If there are any errors in the VBS code, the line above is preventing you
from seeing them. So remove or comment it.
 exapp.Application.AskToUpdateLinks = false
 exapp.workbooks.Open ("C:\Documents and Settings\Jim\Desktop\2011
Rack Daily-MTD-YTD Sales.xls")
 exapp.run"DoAll"  (FYI THISMACROIS ACTALLY CALLING SEVERAL MACROS
- AND IT WORKS FINE IN EXCEL)
' exapp.activeworkbook.save
 exapp.Application.DisplayAlerts = False  'eliminates any alerts or
warnings
Remove of comment this one above too.
 exapp.activeworkbook.close true
 exapp.quit
 set exapp = nothing
Add another msgbox to end of VBScript to indicate when it finishes.
MsgBox "VBScript complete.
Sorry I don't know anything about ESSEXCLN.XLL so I can not offer any help
with prototype API calls.

Thank you! I will try those. I appreciate your help very much.