Excel automation, excel.exe won't quit

[PulsaMatt]

I'm using vbscript to put together a script that automates a horribly manual process for generating daily sales reports. It handles everything from downloading the data from our ERP system, dumping it to Excel, formatting it, saving the file to a central server, and emailing it to selected individuals.

I got the script running perfectly, with the exception of excel.exe not closing when the script is finished running.

I have "option explicit" turned on, and I doublechecked to make sure that every object I created is gracefully set to nothing before the objXL.quit command.

I am not sure why this is happening ... I've googled all over and cant find a solution for vbscript/WSH (can only find VB6 & VB.Net). I am not sure where else to look.

I'd post all my code in here but the problem is that my code is approx 875 lines long (30K in size) ... I am not sure what portions to post if any.

I have another simpler report generator script that handles some smaller reports that was the base for this script I am having trouble with. The simpler version closes Excel fine, and it uses the exact same object creation and object destruction code as the complex version.

Anyone have any ideas or common things I could have overlooked that would cause this?

Any help is created appreciated.


Matt Laski
Web & Systems Support
Pulsafeeder Inc.

 

[markdmac]

Though not a solution to why you are getting different results, how about using Sendkeys to tell Excel to close?

I hope you find this post helpful.

Regards,

Mark

 

[tsuji]

Plausible source I can think of off-hand.
[1] Insert oxl.visible=true to make it visible to see if there are alert box which require custom-interactive. If yes, debug that part to suppress needed interactive, then make it invisible again.
[2] When save, saveas etc, set displayalert to false to avoid interactive.
[3] If somewhere you query builtindocumentproperties (or equivalent ones for other office application package) for some document, you need to quit that application even not explicitly created by createobject.

 

[PulsaMatt]

markdmac ...
How would I use Sendkeys to quit Excel? Would it only close the version of Excel that was spawned by this instance of the script? Its possible multiple reports (and therefore multiple instances of Excel) could be running at once.

tsuji ...
I am running the code with "objXL.visible = TRUE" for debugging ... the script does its job, saves the excel sheet to the network, etc ... when I do objXL.quit() the spreadsheet closes, but excel.exe is still in the background.

I set objXL.displayAlerts = FALSE before the objXL.saveas command ... didnt help.

I don't believe I am using any builtindocumentproperties (I actually dont know what those are or how to use them).

....

I'm still poking around with it ... my last resort may be to write an additional clean-up script to run after all the reports are completed ... this script would kill all instances of excel.exe on the computer running the script (its a dedicated machine that does this stuff).

Matt Laski
Web & Systems Support
Pulsafeeder Inc.

 

[markdmac]

tsuji's solution is better if you can get it working, but using sendkeys is fairly easy.

The problem is that even with using AppActivate to ensure you set your focus to Excel, if there are multiple instances of Excel open you are probably going to run into trouble.

Here is a simple example that will set the active application to notepad and type hello in it followed by hitting the enter key. This assumes you alredy have Notepad open.

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.AppActivate "notepad" 
WshShell.SendKeys "Hello{ENTER}"

You will find a list of the special keys (Like Alt) that you will need here to close Excel.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/wsmthsendkeys.asp

I hope you find this post helpful.

Regards,

Mark

 

[PulsaMatt]

Well ... it turns out that excel quitting is 100% random at this point. I've been playing with it ... the script will make excel.exe quit sometimes, but the majority of the time it won't. Even when I run the same script with all the same command line options ... it will run severals times and kill excel.exe no problem, then it will just decided to leave excel.exe open. Once it wont kill excel.exe anymore I have noticed that the only way it will kill excel.exe again is for me to wait a while before running the script again.

This is really confusing me ... its completely inconsistent, if it would run it with option set A fine, but wouldnt work with option set B, then I would know where the problem is .... but using only option set A its a random guess as to if it will kill excel.exe this time.

Matt Laski
Web & Systems Support
Pulsafeeder Inc.

 

[tsuji]

>my last resort may be to write an additional clean-up script to run after all the reports are completed ... this script would kill all instances of excel.exe on the computer running the script
You can do that but that's not the proper way for a script doing its own job with full control where and when it create instances of excel application.

>I'd post all my code in here but the problem is that my code is approx 875 lines long (30K in size) ... I am not sure what portions to post if any.
If you really want to it can unlock the mystery, unless deciding to cut out irrelevant parts is by itself a real job which takes a lot of decision and judgement. But, then it tests your understanding of and helps you to understand better your own script.