topic Re: Get multiple Excel instances in VBA Forum

Get multiple Excel instances

aammarJHHNU — Mon, 26 Feb 2024 13:27:36 GMT

Hi !

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

Thank you.

Re: Get multiple Excel instances

Ed__Jobe — Mon, 26 Feb 2024 15:43:55 GMT

show your code.

Re: Get multiple Excel instances

aammarJHHNU — Mon, 26 Feb 2024 15:53:19 GMT

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'.

Re: Get multiple Excel instances

Ed__Jobe — Mon, 26 Feb 2024 16:07:10 GMT

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

Re: Get multiple Excel instances

norman.yuan — Tue, 27 Feb 2024 15:23:22 GMT

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.