VBScript: Excel application object statusbar call rejected by callee

1
votes

I have a VBScript that errors when I try to get Excel's status bar string. See comments in code below. I tried putting objExcel.DisplayStatusBar = True in above the erroneous line, but then that line errors. That tells me something about objExcel is going wrong(?). If I put in a msgbox just prior to the erroneous line, it hangs the entire vbs (expected). This vbs runs in the morning so when I see the msgbox popup and click OK all systems have already completed except the vbs that is hanging because of the msgbox. I click OK on the msgbox and I get no error. The reason I am waiting with a timed loop is because macro CreateModel has some Application.OnTime calls in it back to CreateModel, which is necessary for reasons that are beyond this question. The VBScript doesn't 'know' that I have OnTime calls so if I don't 'wait' it will proceed with the rest of the vbs code and mess things up for other reasons. So I have to wait and I use the statusbar to know when all is finished. I can't do a purely timed wait because the processing time of CreateModel and its associated OnTime calls varies quite a bit.

It is a little confusing. Looking for debug suggestions and/or solutions if you have any.

EDIT: if someone knows how to create the error "call rejected by callee" for line sStatus = objExcel.StatusBar, that would help me debug this.

EDIT2: Here is a picture of the error. The script is a .vbs file. I had to grey out the path for my clients protection:

error picture

VBScript:

Dim objExcel, wMn, r, wT
Set objExcel = CreateObject("Excel.Application")

Set wMn = objExcel.Workbooks.Open("Z:\path\Model_*.xlsm")
objExcel.Application.Visible = True


objExcel.Run "'Z:\path\" & wMn.Name & "'!CreateModel"

'wait until model is finished
'have to do this because Application.OnTime is called in CreateModel and vbs doesn't wait 
Dim sStatus
Dim dteWait
Do 

    dteWait = DateAdd("s", 600, Now()) '60 = 60 secs, 600 = 10 mins
    Do Until (Now() > dteWait)
    Loop 

    'objExcel.DisplayStatusBar = True  '<-- if I include this line I get the same error, but for this line
    'msgbox objExcel.StatusBar '<-- when I include this line no error occurs, see notes at top
    sStatus = objExcel.StatusBar    '<-- main error/issue

Loop While not sStatus = "Model Finished"

'more code below, but omitted for clarity
2
vbaexcelvbscript
have you tried sStatus = objExcel.Application.StatusBar – Sorceri
Or just sStatus = Application.Statusbar – mrbungle
@mrbungle and Sorceri - I will give both those a try, but I am pretty sure the Application object doesn't exist in vbs unless I CreatObject() and objExcel.Application is like doing Application.Application in VBA since in my script objExcel IS the Application object. But I'm not a VBS expert, so I'll give both a try and report back. – mountainclimber11
After re-reading your question (and making a lot of assumptions) why not break to the process down into different subs that call each other and when done calls your msgbox? Again, lot of assumptions. – mrbungle
are there other excel instances open? Are we sure excel has finished creating the model. Did you check the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLE: EnableDCOM =Y Try also turning off display alerts. – Sorceri

2 Answers

1
votes

I would designate a cell, give it a named range value ("macroDoneCheck", for example), and have the macro load a "done" value that you can check for in your VBS. The loop for waiting/checking this cell value in your VBS is something like:

Do

  WScript.Sleep(30000) 'wait 30 seconds
  isDone = objExcel.Range("macroDoneCheck")

Loop While not isDone  = "Complete"

Or something like that. You may need to specify the sheet as well, like:

  isDone = objExcel.Sheets("Sheet1").Range("macroDoneCheck")
1
votes

I credited n8. with the best answer because it is a better approach, but this answer is less work and it worked fine.

From my original code/question, wrap sStatus = objExcel.StatusBar like this:

on error resume next
sStatus = objExcel.StatusBar    
on error goto 0

Again, just an alternative to the answer n8. provided. It works because for some reason accessing the statusbar while CreateModel is still running produces and error, but in my case the while loop continues to loop because the while condition has not been met. I knew the while condition would eventually be met, even if accessing the statusbar produces an error some of the time, because when I hang the vbs with the msgbox (see notes in code in original question) everything works with no issues if I let it hang long enough. This may be a specific issue that others may not experience, so take it for what it is worth.