0

So I basically have a VBS script that's supposed to post data to an Excel sheet asynchronously. I currently do this by using GetObject on the workbook's path like so:

Set xlBook = GetObject(strPath & "\Runner.xlsm")

This seems to work fine, except that the workbook will close at the end of the script if it was not open previously (not desired, I have a macro that will close and save the book when necessary).

This is similar to Question 7708039, EXCEPT I want to intentionally keep the excel instance OPEN, not force it to close (the reverse of his problem).

I think it's closing because the variables referencing the object get destroyed at the end of the script, but I can't figure out how to release those handles without destroying them (i.e. set to Nothing).

Community
  • 1
  • 1
Tsaukpaetra
  • 579
  • 5
  • 26
  • 1
    In and off itself, connecting to a running object like excel doesn't close it on disconnect. So something else must be happening. – phd443322 Jun 24 '14 at 06:23
  • That's the problem, sometimes excel is not running at the start of the script. Getting the workbook object will open excel if it is not already open, which leads to the problem. – Tsaukpaetra Jun 24 '14 at 16:05
  • 1
    If it's not running then set it to visible. `ActiveX objects must shut down in the following way: If the object's application is visible, the object should shut down only in response to an explicit user command (for example, clicking Exit on the File menu) or the equivalent command from an ActiveX client. If the object's application is not visible, the object should shut down only when the last external reference is gone. – phd443322 Jun 24 '14 at 17:07
  • 1
    If the object's application is visible and is controlled by an ActiveX client, it should become invisible when the user shuts it down (for example, clicking Exit on the File menu). This behavior allows the controller to continue to control the object. The controller should shut down only when the last external reference to the object has disappeared.` – phd443322 Jun 24 '14 at 17:07
  • @phd443322 This makes the most sense and is accurate to what is happening. It seems the best solution is to make Excel visible then. Maybe you can post your info as an answer so I can accept it? – Tsaukpaetra Jun 24 '14 at 19:50

3 Answers3

3

Instead of getting a reference to a specific workbook, have you tried getting a reference to Excel and then opening the workbook?

' 1a. Get an existing Excel instance...
Set Excel = GetObject(, "Excel.Application")

' 1b. Or, create one. Make it visible for testing.
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True

' Load the workbook...
Set Workbook = Excel.Workbooks.Open(strPath & "\Runner.xlsm")

' Do stuff and save, if desired.

' Close workbook...
Workbook.Close

' Excel stays open. If you want to close Excel, use:
Excel.Quit
Bond
  • 16,071
  • 6
  • 30
  • 53
  • Yes, but this attempts to open a (potentially already open) workbook again, and still results in the workbook being closed at the end of the script. My goal is to have the book remain open after the script ends, and the workbook itself will close itself and excel once it's done with what it needs to do. – Tsaukpaetra Jun 24 '14 at 16:04
  • @Tsaukpaetra: Then simply check if the workbook already is open before attempting to open it, and remove the last 2 statements, so that the workbook isn't closed and Excel keeps running after the script terminates. – Ansgar Wiechers Jun 24 '14 at 16:34
  • The problem seems to be that Excel closes the workbook and itself even without those lines (Code above is not mine). However, [this question](http://stackoverflow.com/a/7717259/1500015) seems promising, I'm going to try and set Excel to be visible and see if that helps anything. – Tsaukpaetra Jun 24 '14 at 16:40
  • 1
    Unless you're using `Excel.Quit`, it's not going to close. You should probably check the processes list in Task Manager to make sure you don't have a number of `excel.exe` processes running. You might be confusing "close" with "not visible". – Bond Jun 24 '14 at 17:08
0

Per phd443322's two comments Comment 1 and Comment 2, this is apparently by design. The solution here is to trick the object (in this case Excel) into thinking the user will need to interact with it or maintain interaction after the reference is destroyed.

Thus, the proper workaround is to make it interactive, in this case using:

xlApp.Visible = True

Thus Excel becomes visible and won't close just because the reference is destroyed.
Since I don't want this instance of Excel visible, I then have the VBS use xlApp.OnTime to call a macro (after one second, plenty of time for the VBS script to have exited) to hide the application window again.
This makes the application blink up on the screen for a second, but it's the best I can do in this instance.

Community
  • 1
  • 1
Tsaukpaetra
  • 579
  • 5
  • 26
  • 1
    Use `GetObject` to get a running (hidden) instance. If you can't get one, launch Excel yourself with `Shell.Run` and pass `0` as the 2nd param to keep the window hidden. Then use `GetObject` to get it. If you shell Excel then you won't have to worry about it shutting down when references go out of scope. – Bond Jun 24 '14 at 22:15
0

I had the same problem using

xlApp.Visible = True

with this call on windows

wscript my-script.vbs C:/path/to/file.xml

But when I changed the separator from / to \ it worked:

wscript my-script.vbs C:\path\to\file.xml
Márcio Reis
  • 1