none
VBS如何建立Excel的回调事件 RRS feed

  • 问题

  •  

    Hi,

    不知道还有多少人在研究VBS这个古老的工具了,因工作需要,拟用VBS以COM组件形式通过createobject方式连接excel. 按照MSDN上的文档,wscript的createobject方法解释如下:

     object.CreateObject(strProgID[,strPrefix]) 
    Arguments
    object
    WScript object.
    
    strProgID
    String value indicating the programmatic identifier (ProgID) of the object you want to create.
    
    strPrefix
    Optional. String value indicating the function prefix.

    其中strPrefix就是回调函数的前缀,按这个文档代码如下:

    on error resume next
    set objExcel=wsh.createobject("excel.application","obj_")
    if err then
    msgbox err.Description
    wscript.quit
    end if
    on error goto 0
    objExcel.visible=true
    objExcel.workbooks.open  "C:\Users\Administrator\Desktop\book1.xlsx"
    
    pending = true
    while pending
        WScript.Sleep(100)
    wend
    
    function obj_SheetChange(sh,target)
    WScript.Echo("You just change ")
    End function
    
    function obj_WorkbookOpen(wb)
    pending = false
    WScript.Echo("You just open ")
    End function
    这个代码执行后可以正常打开excel,但对事件没有反应。如果把COM组件的progid换成internetexplorer,并替换相应的事件则可以正常运行,代码如下:
    on error resume next
    set objExcel=wsh.createobject("internetexplorer.application","obj_")
    if err then
    msgbox err.Description
    wscript.quit
    end if
    on error goto 0
    objExcel.visible=true
    'objExcel.workbooks.open  "C:\Users\Administrator\Desktop\book1.xlsx"
    
    pending = true
    while pending
        WScript.Sleep(100)
    wend
    
    function obj_SheetChange(sh,target)
    WScript.Echo("You just change ")
    End function
    
    function obj_OnQuit()
    pending = false
    WScript.Echo("You just quit ")
    End function
    运行后打开IE浏览器,关闭后弹出对话框,事件正常。推测应该是Excel的问题,我用的是2007版,请大佬给指点一下,谢谢!

    2019年7月2日 9:36

答案

  • Hi,

    这个论坛是讨论和提问vb.net的论坛,你的issue与vbs更相关,你可以去下面的英文论坛提问你的问题。

    https://social.technet.microsoft.com/Forums/scriptcenter/en-us/home?forum=ITCG

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 fr.dong 2019年7月3日 8:31
    2019年7月3日 2:08

全部回复