When multiple instances of Excel are open, how to set instance of Excel a manually-opened file is opened in?

user_13973354 0
Viewed 335

Background: I have a file, AppLauncher.xlsm, that opens App.xlsm in a new instance of Excel, then closes itself. App.xlsm sets Application.Visible = False, then shows a UserForm. This gives the appearance that the UserForm is its own application, unrelated to Excel.

Issue: If the user manually opens another file, the file is opened in the second instance of Excel (the one with App.xlsm open) and makes the Application visible.

Goal: When the user manually opens a file, open the file in either an already open instance of Excel (if one exists) or a new instance of Excel.

What I've Tried/Researched:

  1. Using the Application.WorkbookOpen event to capture the manually-opened Workbook's path and name to then close it and open it in a different instance of Excel; this would work, but it doesn't take into consideration another Workbook.xlsm with code using the Workbook.Open event (the Workbook.Open event fires before the Application.WorkbookOpen event).
  2. Using Access instead of Excel. The VBAWarning registry key is set to 3, requiring all macros to be digitally signed; unfortunately, signing macros in Access appears to be broken.
  3. Using Word or PowerPoint. I assume I would run into the same issue.
  4. Running Object Table (ROT). From what I've read, the purpose of ROT is to not create new instances of an application if there's already one running. I've also read that Excel only registers the first instance of Excel in ROT; using RotView, I've observed that multiple instances of Excel are registered, not just the first instance.

Potential Solution: Remove the ROT entry for the instance of Excel that has App.xlsm open... unsure how to accomplish this using only VBA (using the SendMessage function?).

AppLauncher.xlsm code:

Private Sub Workbook_Open()
    Call Shell("excel.exe /x /s " & """" & ThisWorkbook.Path & "\App.xlsm""")
    ThisWorkbook.Close
End Sub

App.xlsm code:

Private Sub Workbook_Open()
    Application.Visible = False
    UserForm1.Show vbModeless
End Sub

Edit 1: Using Application.IgnoreRemoteRequests = True on the instance of Excel that has App.xlsm open does not appear to work in Microsoft 365.

microsoft365 winapi vba excel windows
1 Answers

I think you can use a window's API to show or hide the application instance.

Here's some code, I think something like this is what you are after.

Option Explicit

Public Declare Function ShowWindow Lib "user32.dll" (ByVal HWND As Long, ByVal nCmdShow As Long) As Long

Public Const SW_HIDE As Long = 0
Public Const SW_SHOW As Long = 5

Public Sub ShowHide()
    ShowWindow ThisWorkbook.Application.HWND, SW_HIDE 'Get the Handle of the application running the form
    UserForm1.Show 'Pop the form open
    ShowWindow ThisWorkbook.Application.HWND, SW_SHOW 'When the form closes, show Excel again
End Sub

I was able to open another Excel workbook, that new Workbook opened normally, while the original Excel workbook with the form remained hidden.

user_48398270
Related