Opening xl in hidden mode via VBS script

[tx12345]

hi


there is some VBS code that is very handy:

on error resume next
set app = createobject("excel.application")
set wb = app.workbooks.open (replace(wscript.scriptfullname,wscript.scriptname,"hello.xls"))
if wb is nothing then app.quit

when using a form concept, one can have excel completely hidden - even on start up - where only the form appears. makes it 'seem' like the prog is all yours and excel is nowhere to be found. so that is a great feature.

there is a glitch, though. if any other files are opened, once they run their routine (in my case database updates), the program quits. but if you never stray from the single file where the form is located, everything is fine.

i was wondering if there is a way to tweak the code to make sure the original file stays open, even though at times other files might open, which would include opening ie to go on the net and do stuff.

many thanks as always

tx

 

[tx12345]

thanks for the eply

i am using xl 2002

wonder what the difference is in this respect ...

the way i a getting around it is to have both files open in separate instances of xl at the same time, then after an update, the user will close the second file . a hack, but it works for now. i'd still like to know why xl 2002 won't allow a close of another file w/o calling it quits on me.

thanks again

tx

 

[tusharm]

Did you duplicate my experiment using XL2002? Did XL quit when book2 closed? Or did you have to use the Windows Task Manager to end XL?

thanks for the eply

i am using xl 2002

wonder what the difference is in this respect ...
{snip}

 

[tx12345]

hi tusharm

thanks again for the reply

OK, so i did what you suggested. when i click on the vbs file i get a lone msg box on the screen with an xl icon showing on the taskbar. the ms says:

"In Book1.xls Open"

click OK

next:

"In Book2.xls Open"

click OK

next:

"In Book2.xls BeforeClose"

click OK

no more msg box appers. but xl is still found running in the process section, with no sign of excel on the taskbar.


not sure what the nuance is here. when i open up 'A' and then have 'A' open 'B' where 'B' runs a routine, then 'B' closes, the entire prog quits with no xl processes left running (after hittin ctrl-alt-del)

tx

ps when i reopen xl the old fashioned way Book1.xls shows up in the recovered window pane, or whatever it is called, on the left.

Did you duplicate my experiment using XL2002? Did XL quit when book2 closed? Or did you have to use the Windows Task Manager to end XL?

thanks for the eply

i am using xl 2002

wonder what the difference is in this respect ...
{snip}

 

[tusharm]

{snip}
no more msg box appers. but xl is still found running in the process section, with no sign of excel on the taskbar.


not sure what the nuance is here. when i open up 'A' and then have 'A' open 'B' where 'B' runs a routine, then 'B' closes, the entire prog quits with no xl processes left running (after hittin ctrl-alt-del)
{snip}

If I understand you correctly, XL2002 "hangs" *correctly* with the bare-bones code. The problem with it quitting then would appear linked to whatever "routine" is doing.

As I suggested in one of the earlier posts, consider putting a "stop" statement before *every* occurence of "application.quit" in *all* workbooks that contain such a statement. See if that code is somehow getting triggered.

Do you have any error handling code such as "On error resume next" or "On error goto {somewhere}?" If so, nix them. That way you may learn some more about an error in your/XL processing.

You should also consider not messing with the Saved property in the BeforeClose event procedure. I've learnt the hard way not to mess with the workbook state in a BeforeClose (or BeforeSave) procedure.

 

[tx12345]

hi tushar

thanks again for the input. i finally figured out the problem. the file with the form is file 'A'. File 'A' wa opening file 'B' and then telling file 'B' to eun a macro inside of file 'B'. when the macro was done (even though the close comand for file 'B' was in file 'A') the prog qould quit.

*but* when i moved the macro to do whatever it was supposed to do over to file 'A', the prog did not quit when the routine was finished! woo hoo!

thaks again for you efforts. you showed me that the issue was 'not' in the vbs code, it was somewhere else.

tx

 

[tusharm]

Glad you got that worked out.

 

[tx12345]

well, i spoke a little too soon.

if the form is 'modal' then it doesnt quit. if it is not modal it does.

dang it takes a long time to debug!

so it turns out the issue is if the routine involving the opening and closing of another workbook relates to whether or not the form/s command is a part of a modal form (wont quit) or a non modal form (will quit)

so if an xl file is opened with the above vbs script, as long as it doesnt involve opening and closing another workbook, it can be a non modal form. but if the form is modal it'll work. but then i am left with the fact that the form i have been using is non modal, so i am right back to where i started. i'll have to see if my prog works in modal - if it does i finally got this thing working



tx

 

[Legacy 98055]

I was still going on the premise that running object table does not track workbooks opened via automation. Irregardless, this seems to solve the problem
Edit your vbs file by adding "Application.UserControl = True". The zip file contains both TX's and Tusharm's examples.

Examples.zip

on error resume next
set app = createobject("excel.application")
app.usercontrol = true
set wb = app.workbooks.open (replace(wscript.scriptfullname,wscript.scriptname,"hello.xls"))
if wb is nothing then app.quit

Then it will work as you originally set it up. I tried the same method with Tusharm's example and it solved that problem as well. The orphaned Excel process is simply there because we did not shut it down. We cannot because there is still a live reference to it and Book1.xls within the VBScript file. So edit Tusharms VBScript file to take care of the Excel.exe.

on error resume next
set app = createobject("excel.application")
app.UserControl = True
set wb = app.workbooks.open (replace(wscript.scriptfullname,wscript.scriptname,"Book1.xls"))
wb.close false
app.quit

 

[tx12345]

hi Tom

that is very slick. it turns out my prog works w/modal as well, but your code will allow for both possibilities.

btw, the second opening was not supposed to be there.

i also found you changed the phrase 'application.run' to 'call'. aside from the benefit of les code, what is the technical difference between the two, if any?


thanks again

tx

 

[Legacy 98055]

"...what is the technical difference between the two, if any?"

I don't know? I use intrinsic statements whenever possible.