Wait for an Excel Macro to Finish Before Continuing
Jim Young
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
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?
Jim Young
>
> - Show quoted text -
Oh boy. Not sure. Here are the macros:
' Copyright 1992-1999 Hyperion Solutions Corporation. All Rights
Reserved.
' RESTRICTED RIGHTS LEGEND:
' 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
Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVKeepOnly Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVZoomIn Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVZoomOut Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVPivot Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVWizard Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVQueryDesigner Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVFlashBack Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVOptions Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVMemberSelection Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVCurrencyReport Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVCascade Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVRetrieveLock Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVLock Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVUnlock Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVSend Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVCalculation Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVConnect Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVDisconnect Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVDatalessNav Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVLinkedObjects Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVRemoveOnly Lib "ESSEXCLN.XLL" () As Long
' The following prototype declares the VBA-specific functions
Declare Function EssVGetHctxFromSheet Lib "ESSEXCLN.XLL" (ByVal
sheetName As Variant) As Long
Declare Function EssVGetMemberInfo Lib "ESSEXCLN.XLL" (ByVal sheetName
As Variant, ByVal mbrName As Variant, ByVal action As Variant, ByVal
aliases As Variant) As Variant
Declare Function EssVFreeMemberInfo Lib "ESSEXCLN.XLL" (ByRef memInfo
As Variant) As Long
Declare Function EssVFreeDataPoint Lib "ESSEXCLN.XLL" (ByRef Info As
Variant) As Long
' The following constants are suitable as actions
' for the EssVGetMemberInfo function
Const EssChildLevel = 1
Const EssDescendentLevel = 2
Const EssBottomLevel = 3
Const EssSiblingLevel = 4
Const EssSameLevel = 5
Const EssSameGenerationLevel = 6
Const EssCalculationLevel = 7
Const EssParentLevel = 8
Const EssDimensionLevel = 9
' The following prototypes declare the Visual Basic
' Extended Spreadsheet macro-equivalent functions
Declare Function EssVCalculate Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal calcScript As Variant, ByVal synchronous As Variant) As
Long
Declare Function EssVCancelCalc Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant) As Long
Declare Function EssVCascade Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal Range As Variant, ByVal selection As Variant, ByVal
Path As Variant, ByVal prefix As Variant, ByVal suffix As Variant,
ByVal level As Variant, ByVal openFile As Variant, ByVal copyFormats
As Variant, ByVal overwrite As Variant, ByVal listFile As Variant) As
Long
Declare Function EssVCell Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ParamArray memberList() As Variant) As Variant
Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal userName As Variant, ByVal password As Variant, ByVal
server As Variant, ByVal application As Variant, ByVal database As
Variant) As Long
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant) As Long
Declare Function EssVFlashBack Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant) As Long
Declare Function EssVGetCurrency Lib "ESSEXCLN.XLL" (ByVal sheetName
As Variant) As Variant
Declare Function EssVGetDataPoint Lib "ESSEXCLN.XLL" (ByVal sheetName
As Variant, ByVal cell As Variant, ByVal Range As Variant, ByVal
aliases As Variant) As Variant
Declare Function EssVGetGlobalOption Lib "ESSEXCLN.XLL" (ByVal Item As
Long) As Variant
Declare Function EssVGetSheetOption Lib "ESSEXCLN.XLL" (ByVal
sheetName As Variant, ByVal Item As Variant) As Variant
Declare Function EssVKeepOnly Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal Range As Variant, ByVal selection As Variant) As Long
Declare Function EssVRemoveOnly Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal Range As Variant, ByVal selection As Variant) As Long
Declare Function EssVPivot Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal Range As Variant, ByVal startPoint As Variant, ByVal
endPoint As Variant) As Long
Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal Range As Variant, ByVal lockFlag As Variant) As Long
Declare Function EssVSendData Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal Range As Variant) As Long
Declare Function EssVSetCurrency Lib "ESSEXCLN.XLL" (ByVal sheetName
As Variant, ByVal currencyIdentifier As Variant) As Long
Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal Item As
Long, ByVal globalOption As Variant) As Long
Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal
sheetName As Variant, ByVal Item As Variant, ByVal sheetOption As
Variant) As Long
Declare Function EssVUnlock Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant) As Long
Declare Function EssVZoomIn Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal Range As Variant, ByVal selection As Variant, ByVal
level As Variant, ByVal across As Variant) As Long
Declare Function EssVZoomOut Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal Range As Variant, ByVal selection As Variant) As Long
Declare Function EssVSetMenu Lib "ESSEXCLN.XLL" (ByVal setMenu As
Boolean) As Long
Declare Function EssVGetStyle Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal styleType As Variant, ByVal dimName As Variant, ByVal
Item As Long) As Variant
Declare Function EssVSetStyle Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal styleType As Variant, ByVal dimName As Variant, ByVal
Item As Long, ByVal newValue As Variant) As Long
Declare Function EssVLoginSetPassword Lib "ESSEXCLN.XLL" (ByVal
sheetName As Variant, ByVal newPassword As Variant, ByVal oldPassword
As Variant, ByVal server As Variant, ByVal userName As Variant) As
Long
Declare Function EssVSetSKUMouse Lib "ESSEXCLN.XLL" (ByVal
fSKUWithMouse As Variant) As Long
Sub ConnTY()
Dim strUSID As String
Dim strPW As String
Dim strCube As String
Dim CubeCount As Integer
Dim USIDcount As Integer
Dim PWcount As Integer
Sheets("Setup").Visible = True
Sheets("Setup").Select
' CubeCount = 1
' Do Until Range("b" & CubeCount).Value = "TYCube"
' CubeCount = CubeCount + 1
' Loop
'
' USIDcount = 1
' Do Until Range("b" & USIDcount).Value = "Essbase User ID"
' USIDcount = USIDcount + 1
' Loop'''
' PWcount = 1
' Do Until Range("b" & PWcount).Value = "Essbase Password"
' PWcount = PWcount + 1
' Loop
strUSID = Range("b1")
strPW = Range("b2")
'strCube = "SUP" & strCube
strCube = "sale"
Sheets("sale").Visible = True
Sheets("sale").Select
Sheets("Setup").Visible = False
x = EssVConnect(Empty, strUSID, strPW, "A0319p58 ", strCube, strCube)
End Sub
Sub RetData()
Dim TempDept As String
Dim RngCount As Double
TempDept = ActiveWorkbook.Name
Sheets("sale").Visible = True
' RngCount = 3
' Do Until IsEmpty(Range("a" & RngCount).Value) = True
' RngCount = RngCount + 1
' Loop
application.Wait Now + TimeValue("00:4:00")
x = EssVRetrieve("[" & TempDept & "]Sale", Range("EssbaseRange"), 1)
End Sub
Sub DisConn()
Sheets("sale").Visible = True
application.DisplayAlerts = False
Sheets("sale").Select
Sheets("sale").Visible = False
x = EssVDisconnect(Empty)
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\xyou\Desktop\In Process\2011 Rack
Daily-MTD-YTD Sales2.xls", _
FileFormat:=xlNormal, password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
application.DisplayAlerts = True
End Sub
Sub GetDate()
'
Sheets("SALE").Visible = True
Sheets("SALE").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(TODAY()-1,EssbaseDate,3,FALSE)"
Range("D2").Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("J2").Select
application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(TODAY()-1,EssbaseDate,2,FALSE)"
Range("J2").Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
application.CutCopyMode = False
Sheets("SALE").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
Sub DoAll()
GetDate
ConnTY
RetData
DisConn
End Sub
Todd Vargo
> 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
> Jim Young wrote:
> > On Apr 28, 9:15 pm, "Todd Vargo" <tlva...@sbcglobal.netz> wrote:
> >> Jim Young wrote:
> >> > 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.
>
> >> > 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")
> >> > - 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
>
> - 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.