none
VBScript: WScript.ConnectObject fails on 64 bit machine

    Question

  • Hi People!

    I've got the following vbscript file:

    sub EX_WorkBookBeforeClose(args)
    	msgBox "Quitting"
    end sub
    
    set EXC = createobject("Excel.Application")
    set WB = EXC.Workbooks.Open("v:\x.xlsx")
    msgBox EXC.Build
    wscript.connectobject EXC,"EX_"
    EXC.Visible=true
    msgBox "Here"
    WB.Close
    msgBox "There"

    Unfortunately the script fails with "WScript.CreateObject: Could not connect". It shows the build, so the object itself exists, but the connect fails.

    What am I doing wrong?

    The script is supposed to run on a win7/64 machine.

    Lots of Greetings!

    Volker


    • Edited by v_he Monday, February 18, 2013 4:37 PM
    Monday, February 18, 2013 4:37 PM

Answers

All replies

  • I am NOT a VBS guy, just a PowerShell guy! But when I test this, if I just omit the line beginning with 'wscript.commectobject... ", the rest of the script seems to work ok - I see the two message boxes, etc.

    IF you try this without the noted line, does your sample now work??


    Thomas Lee <DoctorDNS@Gmail.Com>


    Monday, February 18, 2013 5:04 PM
    Moderator
  • Does it work if you run it using the 32-bit cscript.exe or wscript.exe?

    Bill

    Monday, February 18, 2013 5:47 PM
    Moderator
  • Does it work if you run it using the 32-bit cscript.exe or wscript.exe?Bill

    I'm still not sure what the line:

    wscript.connectobject EXC,"EX_"

    does...


    Thomas Lee <DoctorDNS@Gmail.Com>

    Monday, February 18, 2013 5:50 PM
    Moderator
  • The point of the script is to connect the script to excel so that the EX_WorkBookBeforeClose procedure gets called by excel. Success has been achieved if you see three message boxes.

    • Edited by v_he Monday, February 18, 2013 5:56 PM
    Monday, February 18, 2013 5:51 PM
  • Have you tried doing what I suggest?

    When I do, I get all three and I see the Excel sheet.

    The only thing you need to add is to kill the Excel process at the end of the script.


    Thomas Lee <DoctorDNS@Gmail.Com>


    Monday, February 18, 2013 5:54 PM
    Moderator
  • Unfortunately no.

    Whether I use the syswow64 cmd.exe, syswow64 w/cscript.exe or both (cmd.exe and script.exe) doesn't make a difference.

    Monday, February 18, 2013 5:55 PM
  • I do NOT believe that this is even possible.

    Also, here is a guy, that describes his frustration, trying to get the same thing to work, without ever succeeding:
    http://dailydoseofexcel.com/archives/2009/08/19/using-vbscript-to-monitor-office-eventsor-not/

    What exactly would be the end purpose for this script? perhaps we can do it in some other way, than tapping into the excel events?


    Best Regards
    Claus Codam
    Consultant, Developer
    Coretech - Blog

    • Marked as answer by v_he Monday, February 18, 2013 7:23 PM
    Monday, February 18, 2013 6:07 PM
  • Sorry, my mistake.

    I forgot the build message box.

    What is supposed to happen is that, upon closing of the workbook, the message "Quitting" shows up.

    Monday, February 18, 2013 6:21 PM
  • This line does some magic which allows the vbscript file to consume events.

    Wscript is a predefined object in every vbscript shell. The event mechanism is used by doing two things:

    • calling that connectobject method with the COM object and a prefix string (the latter being EX_ in my example)
    • defining subroutines with names like <Prefix><EventName>, like EX_WorkBookBeforeClose in my example.

    Then these subroutines get called whenever one of those events occurs.

    More details can be found at http://msdn.microsoft.com/en-us/library/ccxe1xe6%28v=VS.84%29.aspx.

    Lots of Greetings!

    Volker

    Monday, February 18, 2013 6:31 PM
  • I see.

    Well, that explains things. Strange, really. Works perfecty allright from the inside of excel (in a .xlsm file), works outside with a .net program:

    using System;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    namespace ConsoleApplication8
    	{
    	class Program
    		{
    		[STAThread]
    		static void Main(string[] args)
    			{
    			Excel.Application EXC = new Excel.Application();
    			MessageBox.Show(EXC.Build.ToString());
    			EXC.Workbooks.Open("v:\\x.xlsx");
    			EXC.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(App_WorkbookBeforeClose);
    			EXC.Visible = true;
    			Console.ReadKey();
    			EXC.Quit();
    			}
    
    		static void App_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
    			{
    			MessageBox.Show("Quitting");
    			}
    		}
    	}
    

    but not outside with a vb script.


    • Edited by v_he Monday, February 18, 2013 6:53 PM
    Monday, February 18, 2013 6:44 PM
  • What exactly would be the end purpose for this script? perhaps we can do it in some other way, than tapping into the excel events?


    The purpose is pretty much exactly that.

    The vbscript runs inside another tool, opens an excel worksheet and then does some stuff in the hosting tool when that workbook gets closed.

    Monday, February 18, 2013 6:48 PM
  • I guess we'll have to channel the event through a .NET dll. With these, the VBScript event mechanism works.
    Monday, February 18, 2013 7:23 PM
  • So if I understand you correctly, you will just need to know, when the workbook has closed?

    How about this then:

    While NOT EXC.ActiveSheet IS Nothing
    Wscript.Sleep 1000
    Wend
    MsgBox "Workbook Closed"

    When the workbook closes, so does all the activesheets, and therefore the while loop ends.

    Best Regards
    Claus Codam
    Consultant, Developer
    Coretech - Blog

    Monday, February 18, 2013 7:23 PM
  • Well, it needs to be a specific one and excel can hold several workbooks open.

    But your polling approach is certainly something to think about since we know the workbook's name. We'll have to check whether the host program (a CAD program) gets blocked during that sleep as well.

    Monday, February 18, 2013 7:31 PM