84157

VBS Start an instance of excel then detach from it

Question:

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.Quit

Answer2:

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

Recommend

  • Aspect advising other aspects
  • Which table should be Parent table and which should be child table?
  • Getting directory of input file (Applescript)
  • Finding parents in a tree hierarchy for a given child LINQ (lambda expression)
  • How to sort things out in ListView?
  • Capturing STDOUT in RSpec
  • ASP.NET MVC2 Error: No parameterless constructor defined for this object
  • Extracting individual digits from a float
  • Dependable views in Ember
  • R h2o.glm - issue with max_active_predictors
  • Copying rows in a database when rows have children
  • Avoid registering duplicate broadcast receivers in Android
  • Java making confirming exit
  • IE6 changes DOCTYPE to a bad one
  • Doctrine/Symfony entity generator and generating entity from one table
  • Default parameter as generic type
  • Invalid Date on validation Date of js
  • how to avoid repetitive constructor in children
  • Spark fat jar to run multiple versions on YARN
  • Ajax Loaded meta Tags
  • Disabling Alt-F4 on a Win Forms NotifyIcon
  • Xamarin Forms - UWP Fonts
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • How to extract text from Word files using C#?
  • Join two tables and save into third-sql
  • Can I make an Android app that runs a web view in Chrome 39?
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • How to model a transition system with SPIN
  • ActionScript 2 vs ActionScript 3 performance
  • Build own AppleScript numerical error handling
  • ORA-29908: missing primary invocation for ancillary operator
  • Arrays break string types in Julia
  • json Serialization in asp
  • LevelDB C iterator
  • Linking SubReports Without LinkChild/LinkMaster
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Does armcc optimizes non-volatile variables with -O0?
  • Converting MP3 duration time
  • Conditional In-Line CSS for IE and Others?