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 <a href="https://stackoverflow.com/questions/7708039/vbscript-code-to-keep-excel-file-open" rel="nofollow">Question 7708039</a>, 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).Answer1:
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.QuitAnswer2:
Per <a href="https://stackoverflow.com/users/3724689/phd443322" rel="nofollow">phd443322</a>'s two comments <a href="https://stackoverflow.com/questions/24376129/vbs-start-an-instance-of-excel-then-detach-from-it#comment37728137_24376129" rel="nofollow">Comment 1</a> and <a href="https://stackoverflow.com/questions/24376129/vbs-start-an-instance-of-excel-then-detach-from-it#comment37728145_24376129" rel="nofollow">Comment 2</a>, 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.<br /> 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.<br /> This makes the application blink up on the screen for a second, but it's the best I can do in this instance.Answer3:
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