Find All Instances of excel and close

[jimlee]

Please could someone point me in the right direction, I need to find all open excel documents and close them. If any are not saved, the user must be prompted and also the script must wait for all excel apps to be closed before proceeding with the rest of the code.

This is a vague 'stab in the dark' at the code, but to be honest i'm just guessing from stuff i've found around the forum. This is my first day on vbScript !! (although I know vba if that's any help)

Any help much appreciated as usual.

jimlad

"There is more to life than simply increasing its speed."
-Mahatma Gandhi

 

[jimlee]

sorry, forgot to add my code....

	dim objExcel
	set objExcel = getobject("Excel.Application")
	objExcel.Visible = True
	objExcel.DisplayAlerts = True
	objExcel.Quit

jimlad

"There is more to life than simply increasing its speed."
-Mahatma Gandhi

 

[mrmovie]

try and use your vba knowledge

For Each aWorkBook In ibjExcel.Workbooks
aWorkBook.Save
Next

something like the above might get you started?? or it might be totally wrong

 

[jimlee]

Hi mrmovie,

Thanks for the response, I don't have any problem using my vba knowledge with regards to the coding, It's just that I can't seem to find out exactly how to set the references correctly. I can open / write to / close an application etc but i've scoured the internet looking for how to:

a) find out how to reference a workbook if you don't know if it's open or not(ie. you don't know it's name) I presume it's along the lines of getObject or something similar but I don't know...and....

b) make the application wait for the user to save any excel files when prompted before the rest of the code continues. I presume some kind of loop whilst looking for instances of excel which are still open (don't want to use the timer as I don't know how long will be needed), but again with the references to excel i'm stumped !!

The annoying thing is that the bit of code that i posted worked earlier today, it gave excel the focus and then prompted for you to save any unsaved worksheets before it shut down (although i didn't figure out how to make the rest of the code wait). However it isn't working now !! i'm not sure if i've changed it in the meantime ?

jimlad

"There is more to life than simply increasing its speed."
-Mahatma Gandhi

 

[tsuji]

>[tt]set objExcel = getobject("Excel.Application")[/tt]
[tt]set objExcel = getobject([red],[/red]"Excel.Application")[/tt]

You should further loop until you've exhausted all excel instances.
[tt]
on error resume next
do while true
set objExcel = getobject(,"Excel.Application")
ir err.number<>0 then
err.clear
exit do
end if
'continue the rest ...
loop
on error goto 0
'continue with other stuff if any
[/tt]

 

[mrmovie]

to reference a work book i think you should just be able to use the Workbooks collection

'using tsuji's code to get the Excel Application reference

For Each aWorkbook In objExcel.Workbooks
'aWorkbook is your dynamic reference to an open workbook
If aWorkbook.Saved = False Then
aWorkbook.Save
'or if it is the first time then you might need
'aWorkbook.SaveAs fname:....
End If
Next

you should find it is as simple as the above code

 

[jimlee]

this...

set objExcel = getobject(,"Excel.Application")

is opening a new instance if excel instead of the one which are already open therefore when I used it in a loop to close all workbooks, all it was doing was opening a workbook, then closing it again and hence an ugly infinite loop on my machine !!

do while true
    set objExcel = getobject(,"Excel.Application")
    if err.number<>0 then
        err.clear
        exit do
    end if
    objExcel.visible = true
    objExcel.displayalerts = true
    objExcel.Quit
    
'continue the rest ...
loop

jimlad

"There is more to life than simply increasing its speed."
-Mahatma Gandhi

 

[tsuji]

>this...
>[tt]set objExcel = getobject(,"Excel.Application")[/tt]
>is opening a new instance if excel instead of the one which are already open therefore when I used it in a loop to close all workbooks, all it was doing was opening a workbook, then closing it again and hence an ugly infinite loop on my machine !!

Meaning? Don't understand what you want to convey. So, you think your original line
>[tt]set objExcel = getobject("Excel.Application")[/tt]
is the right starting point?

I don't get it.

 

[PHV]

set objExcel = getobject(,"Excel.Application") is opening a new instance if excel instead of the one which are already open
Really ?
Have a look here:

http://msdn.microsoft.com/library/en-us/script56/html/vsfctGetObject.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jimlee]

Ok, thanks for the link, this is what I thought getObject should do but i was beginning to wander !

It is now working It started working after I added "dim objExcel" to the code (yes, I know I should have done this in the first place !!).

The really weird thing is that I removed that line again just to check that this was definately the reason... and it's still working !! I really am confused now.

Anyway, hopefully it will continue to work this time ?

The code is still not pausing to wait for the user to close excel though I was under the impression that this is what the err.number bit of the code was for.... I'll have a look round the forum ....

cheers for all your help so far guys as ever it's much appreciated


jimlad

"There is more to life than simply increasing its speed."
-Mahatma Gandhi

 

[jimlee]

Just incase anyone was following this thread I thought i'd post my final code. Thanks again for all your help.

strComputer = "."
dim gExcel
Set WshNetwork = WScript.CreateObject("WScript.Network")
Set WSHShell = wscript.CreateObject("wscript.shell")
Set FSO = CreateObject("Scripting.FileSystemObject")

oldfile = "Test_AddIn (Ver1.5).xla"
newfile = "Test_AddIn (Ver1.6).xla"
oldpath = "I:\Globcust\Corporate actions\System Liaison\VB\"
newpath = "H:\msoffice\XLStart"
UN = ucase(WshNetwork.UserName) 'Get GID
dim TimeOut
dim Response
TimeOut = 0
Response = 0

''''tell the user what's going on & give them chance to quit''''''''''
Response = msgbox ("You are about to run a macro which will update excel" & vbcrlf & _
	"Excel will be closed (you will be asked to save any unsaved workbooks first" & vbcrlf & _
	"Do you wish to continue?",36, "ATTENTION!")
if Response = 6 then
	dim objProcessList
			''''''shut down excel'''''''
			on error resume next
			Set gExcel = GetObject(,"Excel.Application")
			gExcel.visible = true
			gExcel.displayalerts = true
			gExcel.activeworkbook.close
			gExcel.application.quit
			gExcel = ""
			on error goto 0

			do   ''''''start a loop to wait for all instances of excel to close before continuing'''''
			    ''''''find excel in task manager process list''''''
			    Set objWMIService = GetObject("winmgmts:" _
				& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
			    Set colProcessList = objWMIService.ExecQuery _
				("Select * from Win32_Process Where Name = 'Excel.exe'")
			    x = false
			    '''''if excel is open change x to true'''''''
			    for each objprocess in colprocesslist
				x = true
				'msgbox objprocess.name
			    next    
			    ''''''if x is false then no excel apps are open so we can carry on with the update''''''''''
			    if x = false then 
				exit do
			    end if
			    ''''''ease the pressure on the old cpu whilst the user sorts his s#@t out'''''''
			    wscript.sleep 2000
			    '''prevent an infinite loop incase the user has gone home or cancelled the excel shutdown !!''''
			    Timeout = TimeOut + 2000
			    'msgbox "timeout = " & timeout
			    ''''I give the user 2 minutes to comply, otherwise, terminate process'''''
			    if timeout => 180000 then
				msgbox "macro update timed out, please close any open excel spreadsheets and try again"
				wscript.quit
			    end if     	
			loop


	''''now excel has closed  the startup folder can be updated / created'''''
	'''create appropriate folders and copy xla''''''''
	    If Not (FSO.folderexists(newpath)) then 'creates XLStart if there isn't a folder
	    	FSO.createfolder(newpath)
	    	FSO.CopyFile oldpath & newfile, newpath & newfile, True 'copy new file
	    End if
	    If FSO.FileExists(newpath & "\" & oldfile) then 'deletes old version
	    	FSO.DeleteFile(newpath & "\" & oldfile)
	    End if
	    FSO.CopyFile oldpath & newfile, newpath & "\" & newfile, True 'copy new file
	    Info = Msgbox ("Macro Update Complete!", 64, "Information")

	msgbox "Excel Add-In was updated"
else
	msgbox "No update performed"
end if

jimlad

"There is more to life than simply increasing its speed."
-Mahatma Gandhi

 

[mrmovie]

hi Jimlad, sorry to say this but your script wont actually shutdown more than one excel.exe thread.....

you have no looping for the excel close stuff.

 

[jimlee]

Doh !! you're right...

although this scenario will be caught here..

if timeout => 180000 then
    msgbox "macro update timed out, please close any open excel spreadsheets and try again"
    wscript.quit
end if

I haven't got time to fix it now, but will post when doone.

jimlad

"There is more to life than simply increasing its speed."
-Mahatma Gandhi