VBA Switch from an open excel app to a different open Excel app

Question

I'm using VBA Excel 2010 which allows several instances of the Excel app to be open at the same time. I need to import data from workbook2 (which is open in a different Excel app), into workbook1 which is in the current Excel app. I have found that APPACTIVATE will determine if workbook2 is in fact open or not, even if in a different Excel app. If not currently open, it's easy...I just open workbook2 within the current Excel app, get my data, update workbook1 then close workbook2. But if workbook2 is already open, I need to switch to the workbook2 app, get my data, update workbook1 and leave the workbook2 untouched. Is there anyway I can access workbook2 app?

Sub AppAct()

    'Workbook (1): workbook.name="Book1"
    'Workbook (2): workbook.name="Fuel2010I.xlsm"
    'Workbook (2): workbook.fullname="C:\FltTools\Fuel2010I.xlsm"
    'Sheets("FL") exists in Workbook (2)
    'Sheets("S") exists in Workbook (1)
    
    On Error Resume Next
    AppActivate ("FUEL2010I.xlsm")
    If Err.Number > 0 Then 'if workbook not already open, then open it and select sheet "FL"
        On Error GoTo 0
        Workbooks.Open Filename:="C:\FltTools\Fuel2010I.xlsm"
        Sheets("FL").Select 'works fine
    Else 'if the workbook is open, select sheet "FL"
        On Error GoTo 0
        Sheets("FL").Select 'Subscript out of range, I'm not talking with FUEL2010I.xlsm
    End If
    Sheets("S").select
End Sub

I've tried the code as above. The code does not switch to the workbook2 instance of Excel

There is a large difference between two workbooks open in the same instance of Excel, and two instances of Excel with separate workbooks. If both workbooks are open in the same Excel, you can just look at `Application.Workbooks` and have full access to the workbook object. If they are open in separate instances, then you have almost no control over the workbook. — Toddleson, Mar 29 '23 at 16:36
`Application.Workbooks.Open` will open a workbook in the current application. It doesn't have anything to do with creating a second application or opening a workbook in a separate Excel App. Are you're talking about a separate application? — Toddleson, Mar 29 '23 at 16:38
What happens when you try `Application.Workbooks("FUEL2010I.xlsm").Sheets("FL").Activate`? — Toddleson, Mar 29 '23 at 16:41
If you know the full path to the workbook open in the other instance I think you can use `GetObject()` to get a reference to it (see https://stackoverflow.com/a/15587921/478884). Or see this post for a more flexible approach- https://stackoverflow.com/questions/30363748/having-multiple-excel-instances-launched-how-can-i-get-the-application-object-f — Tim Williams, Mar 29 '23 at 16:45
This is solved but I don't know how to mark it as such. I believe it was Tim Williams' direction to an earlier post that did the trick... "Set xlApp = GetObject("C:\FltTools\Fuel2010I.xlsm").Application". Thank you all!!!! — Balloon Tom, Apr 01 '23 at 14:50

VBasic2008 · Answer 1 · 2023-03-29T17:42:15.013

Switch Between Excel Instances Using `AppActivate`

After app-activating, you need to activate the window as illustrated in the following code.

A Quick Fix

Sub AppAct()
    
    Dim ErrNumber As Long

    On Error Resume Next
        AppActivate "FUEL2010I.xlsm"
        ErrNumber = Err.Number
    On Error GoTo 0
    
    If ErrNumber > 0 Then ' workbook is not open in another instance
        Workbooks.Open Filename:="C:\FltTools\Fuel2010I.xlsm"
    Else ' workbook is open in another instance
        Windows("FUEL2010I.xlsm").Activate
    End If

    Sheets("FL").Select
    Sheets("S").Select

End Sub

VBA Switch from an open excel app to a different open Excel app

1 Answers1

Switch Between Excel Instances Using AppActivate

Switch Between Excel Instances Using `AppActivate`