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

0 votes

I can have multiple Excel 2010 instances open at once because I'm using VBA Excel 2010. I have workbook1 open in the current Excel app, and I need to import data from workbook2 (which is open in a different Excel app). I've discovered that, even if workbook2 is open in another Excel app, APPACTIVATE will tell whether it is or is not. If not open right now, it's simple... Within the present Excel application, I simply open workbook 2, get my data, update workbook 1, and then dismiss workbook 2. So if workbook2 is already open, I have to access it, retrieve my information, update workbook1, and leave workbook2 alone. Can I access the workbook2 app in any way?

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

Mar 30, 2023 in Others by Kithuzzz
• 38,010 points
264 views

1 answer to this question.

0 votes

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
answered Mar 30, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel VBA: how to find a description from an AD-group

First add the 'description' property to your ...READ MORE

answered Feb 16, 2023 in Others by Kithuzzz
• 38,010 points
575 views
0 votes
1 answer
0 votes
1 answer

How to open an Excel Online document as a PDF

In light of your comment above, I ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,420 points
342 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
3,010 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
915 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,239 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
528 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
769 views
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,420 points
590 views
0 votes
1 answer

Excel VBA - Need to get a specific line from TextBox and send to an specific Cell

Moving your string into an array first ...READ MORE

answered Feb 2, 2023 in Others by narikkadan
• 63,420 points
487 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP