Need to close all instances of excel

[emorris1000]

I have a program I have written that does a bunch of wacky stuff in excel. Before the program runs though, it is imperative that it runs through and closes ALL instances of excel before executing. This is due to an issue with the files that are going to be manipulated are often open on peoples desktops, which messes up the program.

Originally I just used the following line and would have to go to the taskbar and manually shut down any visible or invisible instances of excel:

Set xlApp = GetObject("", "Excel.Application")

Now I am trying to make something a little less derp. This is what I have, but it has a couple of problems:

Public Sub CloseAllExcel()
On Error Resume Next
 
Dim xlApp As Excel.Application
Dim i As Integer
 
i = 0 
 
Do While i < 50   'to avoid infinite loop
 
    Set xlApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Err.Clear
        Exit Do
    End If
    xlApp.Visible = True
    xlApp.DisplayAlerts = True
 
    xlApp.Quit
 
    Loop
 
End Sub

Now, this works, letting me go throught the open files and gives the user the opportunity to close them, but on the last loop it crashes on the xlapp.visible line (saying there is no application) and leaves an invisible instance of Excel.

If I run it without any instances of excel open it crashes on the getobject command. This is expected, but the on error should send it to the if command.

Not sure I entirely understand the getobject command. Everywhere I read it says that its only there to find an existing instance, but I could SWEAR that it is creating them in some situations.

seems that the on error is the main issue....


Edit: Ok I am dumb. It was set to break on all errors.....

 

[MarkK]

If you do something like this you don't have to bother the user with it, it just saves and closes everything...

Public Sub CloseAllExcel()
On Error GoTo handler
 
   Dim xl As Excel.Application
   Dim wb As Excel.Workbook
 
   Do While xl Is Nothing
      Set xl = GetObject(, "Excel.Application")
      For Each wb In xl.Workbooks
         wb.Save
         wb.Close
      Next
      xl.Quit
      Set xl = Nothing
   Loop
   Exit Sub
   
handler:
   If Err <> 429 Then 'ActiveX component can't create object
      MsgBox Err.Description, vbInformation
   End If
 
End Sub

Also, your endless loop protection doesn't work if you don't i = i + 1 somewhere.
Cheers,
Mark

 

[emorris1000]

Also, your endless loop protection doesn't work if you don't i = i + 1 somewhere.
Cheers,
Mark

wow....yeah I need to get some coffee

Anywho, there is no way I can have the code make the choice about what is saved and what isn't. Its a pretty good way to have my boss scream at me.

Followup. Its working now (was just the break on all errors causing the problem) but I am having some weird issues with closing excel still, even with this. It happens sometimes, and doesn't others.

Basically all visible excel things close, but occasionally there will be an Excel process in the task manager. I believe this has something to do with a need to set xlapp = nothing before I close it, or to set the display alerts to false before I close it.....but I'm not sure about this.

 

[MarkK]

The code doesn't make a choice of to save or not to save. It saves and closes everything. The boss will love it. Saves him mouse clicks.
Yeah, it seems weird Excel would run and GetObject wouldn't find it.

 

[emorris1000]

I can't have it save everything. A lot of the files that people open up are raw datafiles. They will manipulate them and do stuff to them, but its understood at the office that you should NEVER save them after doing that, as they are raw datafiles. Hence I can't have them save everything.

Still having issues with there being a remaining instance of Excel after the full uploader runs.

 

[emorris1000]

UPDATE

The problem has something to do with making a "unqualified method or property call". If you do that it creates an invisible reference to the excel application, which makes it impossible to programmatically close Excel.

http://support.microsoft.com/kb/319832/en-us

Now, I feel I have done a good job with making sure my references to excel are explicit/qualified. But I'm not sure if I am doing it right in my main code. I have to deal with it on a sheet and workbook basis, so I've set it up as follows:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
 
....stuff...
 
Set xlApp = New Excel.Application
 
....
 
xlApp.Visible = False
xlApp.ScreenUpdating = False
 
.....
 
xlApp.Workbooks.OpenText filename:="" & StrFilePath & fname & "", DataType:=xlDelimited, Tab:=True
 
 
....
 
[B]Set xlBook = xlApp.ActiveWorkbook[/B]    'this one may be a problem
 
Set xlsheet = xlBook.Worksheets(1)
 
With xlsheet
                .Range("A4:K5").Copy
                .Range("M1:W2").PasteSpecial
 
'.... lots of range reformatting etc  ......
 
                [B].Range(Cells(7, 1), Cells(lastrow, 1)).Value = polyid[/B]  
 
'........This one occasionally throws "Error 462: The remote server machine does not exist or is unavailable", which has to do with unqualified methods........
 
....more stuff....
 
Set xlsheet = Nothing
Set xlBook = Nothing
xlApp.DisplayAlerts = False
xlApp.Quit
Set xlApp = Nothing

Somewhere in here I am doing something that is making an unqualified reference, but I don't get what it is.


..........


Edit: Ok this is weird. When I hit the stop button in the VBA editor is gets rid of the invisible instance of excel. Regardless of whether a module is running....

 

[emorris1000]

UPDATE: SOLVED

This was the offending code that caused the unqualified reference (assume all variables are dimmed apropriately):

   xlsheet[B].Range(Cells(7, 1), Cells(lastrow, 1)).Value = polyid[/B]

Which looks like it is a qualified reference to xlsheet, but its not. This is:

xlsheet.Range(xlsheet.Cells(7, 1), xlsheet.Cells(lastrow, 1)).Value = polyid

This was quite a pain the butt to find, mainly because it was a bit of a hidden unqualified reference. Glad I found it though!