Get multiple Excel instances

[aammarJHHNU]

Hi !

How can I retrieve all opened Excel instances? Currently, I'm only able to retrieve one instance.

Thank you.

[Ed__Jobe]

show your code.

[aammarJHHNU]

    Set EXCELApplication = GetObject(, "Excel.Application")
    If EXCELApplication.Workbooks.Count = 0 Then
        MsgBox "No Excel file is open"
        Exit Sub
    End If
    On Error Resume Next
    Set ExcelWorksheet = EXCELApplication.Workbooks("AAA").Sheets.Item("Update")
    If Err.Description <> "" Then
        MsgBox "Verify that the file is open", vbInformation, "File not found"
        Exit Sub
    End If

Sometimes, even when the file is open, I get the message 'No Excel file is open.' Or, when two instances are open and one of them is my file, I get the message 'Verify that the file is open'.

[Ed__Jobe]

What exactly are you trying to do. The GetObject funtion will only get an app from the Running Object Table if xl is open already.

Use this function to get an xl app object.

Public Function GetXL() As Application
    On Error Resume Next
    Dim xlApp As Application
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    Set GetXL = xlApp
End Function

[norman.yuan]

GetObject() would return only one instance of Excel application, if there are multiple instances running, and there is no guarantee the one in interest is returned (there is why you get "Verify..." message). There is a way to (complicated to most VBA coders, I'd say), to identify which instance is the one you want.

If I were you, I'll simply prompt user to either open the Excel sheet file with Excel (if there is no Excel is running), or close other Excel instances (if multiple instances are running). While this would be simplest/quickest solution, automating running Excel app (or the other way around by automating AutoCAD from Excel VBA) is hardly a good solution, IMO.