VBScript: Excel application object statusbar call rejected by callee

Question

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

@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, Sep 05 '14 at 16:32
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, Sep 05 '14 at 17:01
@mrbungle - I don't follow. The goal isn't to have a msgbox. My goal is to check statusbar via vbs for the end of my macro. I put the msgbox stuff in there to show that objExcel.StatusBar works initially in my vbs, but eventually stops working unless CreateModel and all it's OnTime calls to CreateModel complete. I have to do it via VBScript, because of reasons that are beyond this question (but if you must know they are touched on here: http://stackoverflow.com/questions/8669845/bloomberg-data-doesnt-populate-until-excel-vba-macro-finishes ). — mountainclimber11, Sep 05 '14 at 17:13
@mrbungle - sStatus = Application.Statusbar doesn't work in vbs. You do have to get the application object first. — mountainclimber11, Sep 05 '14 at 17:51
@Sorceri - objExcel.Application.StatusBar do NOT throw an error so I will give that a try, but since objExcel.StatusBar is working in the msgbox, I don't understand how it would help matters. — mountainclimber11, Sep 05 '14 at 17:56
if anyone knows how to produce the error I am getting with sStatus = objExcel.StatusBar that would be helpful. — mountainclimber11, Sep 05 '14 at 18:00
Would you entertain other ways to see if the macro is done processing? For example, have the macro write a value in a cell and have your VBS loop with a 30 second wait interval, checking for the "proceed" value in that cell? — n8., Sep 05 '14 at 19:48
@n8. - That could work, but I have never done that via a vbscript. — mountainclimber11, Sep 05 '14 at 19:54
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, Sep 05 '14 at 20:19
@Sorceri - there very well could be two instances open with the aforementioned OnTime calls within CreateModel. It is difficult to monitor it. I bet that is it. I'll have to build a test case and prove it for my own understanding. In the end, rather than program around that. I think I'll try the answer given by n8. below. Seems like a pretty clean approach. — mountainclimber11, Sep 05 '14 at 20:39

score 1 · Accepted Answer · answered Sep 05 '14 at 20:08

1

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")

answered Sep 05 '14 at 20:08

n8.

1,732
3
16
38

I like that idea. Its clean and independent of the statusbar which changes for the users as well. I'll try it and report back. Thanks! I really would like to know what is going on with my original question/issue though. – mountainclimber11 Sep 05 '14 at 20:15
It's a curiosity to be sure. Some parts of Excel is more accessible to VBA than others, and I tend to shy away from the more complex things unless they are well documented. Good luck! And let me know if it doesn't work, the above it pseudo-code, so it might need tweaking. – n8. Sep 05 '14 at 20:27

mountainclimber11 · Answer 2 · 2014-09-08T17:25:26.003

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.

I like using `on error goto 0` as it will enable error checking again. Not sure if `Err.Clear` does the same thing. Thanks for the cred! — n8., Sep 08 '14 at 17:04

VBScript: Excel application object statusbar call rejected by callee

2 Answers2

Linked