Doesn't recognize opened workbook

[azizrasul]

When I loop through the 2 open workbooks using vba it only recognizes one?

Dim xlWB As Excel.Workbook
Dim objExcelApp As Excel.Application

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

For Each xlWB In objExcelApp.Workbooks
    Debug.Print xlWB.Name
Next xlWB

Set objExcelApp = Nothing
Set xlWB = Nothing

One of the workbooks is a xlsm file and the other is a xlsx file. It doesn't recognize the xlsx file. Tried opening a separate xlsx file and it recognizes that.

If I have only the rogue xlsx open on it's own, then the code recognizes it!

I essentially want to work on 2 Excel files in my code.

 

[Norie]

Are the 2 files open in separate instances of Excel?

 

[azizrasul]

Yes

 

[Norie]

When you loop through objExcelApp.Workbooks you will only return the workbook(s) open in the instance of Excel that objExcelApp refers to.

 

[azizrasul]

OK I understand.
How do you refer to multiple workbooks that are already open in their respective Excel instances?

 

[Micron]

Although two open workbooks appear in the task bar as separate icons, I always thought only one instance of Excel was actually running because if I minimize everything else, I can only find one Excel window. Furthermore, from one Excel workbook I can switch to the other workbook from the current one using the Window option on the View tab of the ribbon. That being said, I haven't worked with two open workbooks at the same time, but I'd suspect you'd have to declare them both then act on them individually. Like

Dim objExcelApp As Excel.Application
Dim xlWB1 As Excel.Workbook
Dim xlWB2 As Excel.Workbook

On Error GoTo errHandler

Set objExcelApp = GetObject(, "Excel.Application")
Set xlWB1 = pathToFirstWorkbook
Set xlWB2 = pathToOtherWorkbook

do stuff to each workbook

ExitHere:
Set xlWB1 = Nothing
Set xlWB2 = Nothing
Set objExcelApp = Nothing
Exit Sub

errHandler:
msgbox "Error " & Err.Number & ": " & Err.Description
Resume ExitHere

 

[xenou]

Separate instances can be used by running Excel from the start menu twice (instead of, for instance, running Excel from the start menu once, then opening two workbooks).

 

[Micron]

We are creatures of habit! I always use the icons in my secondary task bar and open additional books from the "recent documents" list. Thanks for a lesson from class 101!
Then from Access, using vba, would you open one instance and two workbooks or two instances? I see no advantage to doing the latter...

 

[xenou]

Then from Access, using vba, would you open one instance and two workbooks or two instances? I see no advantage to doing the latter...

My preference would be to open one instance. But, alas, this wouldn't help the OP - that code is not opening any instances at all but rather using GetObject to hook an already running instance. I have no idea how you would tell it to get a second one and not a first one. Personally I have always avoided GetObject since it seems a little hard to be sure it's always working the way you want it to.