5

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.

Strithken
  • 66
  • 5
  • You could have something in the `App.xlsm` that checks whether it is visible or not while the form is active/open, and if it is visible then switch it off. – Dean Jan 05 '22 at 16:19
  • 1
    This is something I've explored too: getting a 'private' instance of Excel. One thing that might be possible is to create a new Excel process (you can use the /x command line option to force a new instance) with the command line switch to open a particular named dummy file. Then search in the ROT for that document object, and work back from that to the Excel dispatch interface. – DS_London Jan 05 '22 at 16:50
  • @Dean Thanks for the recommendation. That wouldn't solve the issue as the intent is for manually-opened workbooks to open in a separate instance to not interfere with `App.xlsm`. – Strithken Jan 05 '22 at 17:21
  • @DS_London Thank you. I'll read up on Dispatch Interfaces. I'm very new to APIs and have only recently read about ROT. Do you think removing the ROT entry for the application will resolve the issue? – Strithken Jan 05 '22 at 17:27
  • 1
    @Strithken I am not sure you can 'Revoke' a ROT entry unless you have the ID that was created when the object was registered. This ID is usually held by the app that registered the object in the first place. – DS_London Jan 05 '22 at 17:38
  • At least there are 3 ways to run your VBA-application separately: 1) Run application under another user. 2) Change Office settings to open every document by own Excel process instance (`excel /x`). 3) Migrate your VBA-code to another application (I would prefer `Word`). P.S. I thought `ActiveWorkbook.Windows(1).Visible = False` can hide App.xlsm. But on other xls-workbook closig - App.xlsm also closes. – Daemon-5 Jan 10 '22 at 07:00

2 Answers2

0

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.

Ryan Wildry
  • 5,612
  • 1
  • 15
  • 35
  • It appears that has the same impact as `Application.Visible = False` and `Application.Visible = True`. Thanks for the answer, though. – Strithken Jan 07 '22 at 00:32
  • I think I misunderstood the ask. Did you want a new workbook to be opened in an existing Application instance? – Ryan Wildry Jan 07 '22 at 00:52
  • I want a workbook that is opened through File Explorer to open in an instance of Excel that is not the one that has App.xlsm open. If another existing instance is available, use that one; otherwise, open the file in a new instance. – Strithken Jan 07 '22 at 00:57
0

My solution was to use a batch file. The batch script first opens a named excel file. Then follow this with opening the excel app file using /x to open it in a new session. Double-clicking on another excel file opens it in the first registered session, leaving the excel app alone. This is okay so long as the user doesn't close the first excel file. I confess this works well in win10 with Excel 2010 but I am finding it a little more erratic in Win11 with Excel 365.

Mike Barrett
  • 1