Close multiple Excel instances from Access

[TP69]

I am trying to close (2) different Excel instances from Access. I am using the code below but it only allows me close one instance at a time before it produces the error. It can not get the 2nd instance unless I run it again. Why is this happening and how can I write the code to close all the instances without having to rerun the code.

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

 

[Rx_]

Lets back up just a bit.
Did you open (or create) the Excel objects using Access code to start with?
If the answer is yes, then there might be one solution.
If the andwer is no, then we might look at another one.

also: have you set I to 10,000? 5 might not give the processor a window of opportunity.

 

[Rx_]

It seems to be a timing issue. A loop of 100000 wasn't enough to locate the next running Excel on my workstation.
Five seconds seems like a long time, experiment with the Pause Time, perhaps 1 or 2 will work.

Was able to open up 5 copies of Excel, this closed every one of them, then had an error when there were no more.

Sub CloseAllExcelInstance()
      ' Rx seek and destroy all open Excel fugitives
      Dim PauseTime As Long
      Dim y As Long
10    On Error GoTo MyError
20    For y = 1 To 10 ' would you ever have more than 10 Excel open?          
30        PauseTime = 5    ' Set duration in seconds
40        Start = Timer    ' Set start time.
50        Do While Timer < Start + PauseTime
60        DoEvents    ' Yield to other processes.
70        Loop
80        Set ObjXL = GetObject(, "Excel.Application")
90            Debug.Print "Closing one Excel Instance"
100           ObjXL.Application.DisplayAlerts = False
110           ObjXL.Workbooks.Close
120           ObjXL.Quit
130           Set ObjXL = Nothing
140           DoEvents ' probably optional
150   Next y
160   Exit Sub
MyError:
170       Debug.Print Err.Description ' when no more instance exist shows "ActiveX component can't create object"
End Sub

 

[TP69]

They are instances not created by Access. And thank you this did the trick.... I spent hours trying to figure out why this would not work and never thought of the time delay... I actually put a secondary loop around my original code and that worked as well but still bothered me not be able to understand why it did not work... Thanks again.

 

[Rx_]

Glad it worked out. I wasted time with the loops too. Just no way to tell how much time a processor gives a thread for the time-slice allocation.
If you are createing these from within access code, you could create an array of pointers (objects) and keep them at a higher scope. Then the code would have the pointer (memory location) to close each of them down.
However, if this does the trick for you, maybe creating a dynamic array of object types to manage that wouldn't be worth it.