VBScript:被调用方拒绝Excel应用程序对象状态栏调用 [英] VBScript: Excel application object statusbar call rejected by callee

问题描述

我有一个VBScript，当我尝试获取Excel的状态栏字符串时出错.请参阅下面的代码中的注释.我尝试将objExcel.DisplayStatusBar = True放在错误的行上方，但随后该行出错.这告诉我有关objExcel的信息有误(?).如果我在错误行之前放入msgbox，则它会挂起整个vb(预期).该vb在早上运行，因此当我看到msgbox弹出窗口并单击OK时，除由于msgbox而挂起的vb之外，所有系统均已完成.我在msgbox上单击确定"，但没有任何错误.我等待定时循环的原因是因为宏CreateModel中有一些Application.OnTime调用返回给CreateModel，这是超出此问题之外的原因所必需的.VBScript不知道"我有OnTime调用，因此，如果我不等待"，它将继续处理其余的vbs代码，并由于其他原因而使事情混乱.因此，我必须等待，然后使用状态栏来了解何时完成所有操作.我无法进行纯粹的定时等待，因为CreateModel及其关联的OnTime调用的处理时间相差很大.

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.

如果某人知道如何为sStatus = objExcel.StatusBar行创建错误被呼叫者拒绝的呼叫"，那么这将有助于我进行调试.

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

这是错误的图片.该脚本是一个.vbs文件.我必须为客户保护走出一条灰色路:

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

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

推荐答案

我将指定一个单元格，为其指定一个命名范围值(例如，"macroDoneCheck")，然后让宏加载一个您已完成的"done"值可以在您的VBS中进行检查.在VBS中等待/检查此单元格值的循环类似于:

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

这篇关于VBScript:被调用方拒绝Excel应用程序对象状态栏调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！