Use Excel VBA to run file in own separate instance of Microsoft Excel

Question

(Minimum requirements: Excel 2010 and Windows 7) I have managed to use Bill Manville’s answer found in MSDN with minor changes. The suggested recursive code basically uses files’s Workbook_Open to create a separate instance and taht instance opens the file as editable with no prompts for read-only access.

Private Sub Workbook_Open()
  Dim oXcl As Excel.Application
  If Workbooks.Count > 1 Then
    ThisWorkbook.Saved = True
    ThisWorkbook.ChangeFileAccess xlReadOnly
    Set oXcl = CreateObject("Excel.Application")
    oXcl.ScreenUpdating = False
    oXcl.Visible = True
    oXcl.Workbooks.Open fileName:=ThisWorkbook.FullName, ReadOnly:=False
    AppActivate oXcl.Caption
    ThisWorkbook.Close SaveChanges:=False
  Else 
    Call Continue_Open
  End If
End Sub

The code works very well when Excel is already running as it creates a new instance of Excel and if a new Excel file is opened, it goes to a different Excel instance (running prior to it). But if the file with the Workbook_Open is the one that starts Excel, any further Excel files opened by double-clicking open within that Excel instance as it is the earliest run instance thus ceasing to be separate.

I have got as far as to be able to tell (Windows) whether that file starts Excel by using

Function NumberOfExcelInstances()
  strComputer = "."
  Set objWMI = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
  Set proc = objWMI.ExecQuery("Select * from Win32_Process Where Name = 'Excel.exe'")
  NumberOfExcelInstances = proc.Count
End Function

But I have not been able to find a way to tell NOT to use that Excel instance when opening new files. Any code should be bundled inside the Excel file with the Worbook_Open code. How could I possibly include VBA code inside a file so that it opens in a separate Excel instance even when that file is the one that fires Excel?

Answer 1

After research on code at Application level, a working solution have been found. I am posting it, in case it is of interest to someone else.

When workbook opens the fist time it sets a workbook open event subroutine at Application level (rather than at Workbook level).

When a new workbook opens, the sub at Applictaion level opens a new instance with the workbook to be kept separate by recursivity - closes that workbook in the application instance that checks being separate thus removing the event handler from the application instance and sets that event handler and code on the newly created application instance.

All relevant code is included and it needs to be in three different modules.

1-a VBA Class Module named cXlEvents is created with the following code:

'VBA Class Module named cXlEvents 
Public WithEvents appWithEvents As Application
'Instance variables
Dim sEventSetterPath As String
Dim sEventSetterName As String

Private Sub appWithEvents_WorkbookOpen(ByVal Wb As Workbook)
    Call NewAppInstance(Wb, sEventSetterPath, sEventSetterName)
End Sub

2-ThisWorkbook Module includes:

'1-ThisWorkbook VBA Module calling events at 
'Workbook level.
'2-At Workbook Open set Application level event 
'handler and then instance code by calling subs 
'held in VBA standard module.  
Private Sub Workbook_Open()
  Call SetEventHandler
  Call NewAppInstance(Me)
End Sub

'Code to call "undo" special settings upon opening 
'when file closes
Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Call UndoSettings
End Sub

3-All code necessary to create an instance at Workbook level Open event from a class which will end up at Application level is in a standard VBA Module:

'In a VBA standard Module
Dim oXlEvents As New cXlEvents

Sub SetEventHandler()
  If oXlEvents.appWithEvents Is Nothing Then
    Set oXlEvents.appWithEvents = Application
  End If
End Sub

Sub NewAppInstance(wbWbook As Workbook, Optional sEventSetterPath As String, Optional sEventSetterName As String)

  Dim oXcl As Excel.Application
  Dim wbEventSet As Workbook
  Dim lCaseNum As Long
  Dim sResetMacro As String: sResetMacroName = "UndoSettings"

  'Set instance variables
  sEventSetterPath = ThisWorkbook.FullName
  sEventSetterName = ThisWorkbook.Name

  If wbWbook.ReadOnly And wbWbook.FullName = sEventSetterPath Then
    MsgBox "Already open - please use open file.", , "WARNING"
    wbWbook.Close False
    Exit Sub
  End If

  If Workbooks.Count > 1 Then

    If wbWbook.FullName <> sEventSetterPath Then
      lCaseNum = 1
      Set wbEventSet = Workbooks(1)
      wbEventSet.Save
      Application.Run "'" & sEventSetterName & "'!'" & sResetMacro & "'"
    Else
      lCaseNum = 2
      Set wbEventSet = wbWbook
      wbEventSet.Saved = True
    End If
    wbEventSet.ChangeFileAccess xlReadOnly
    Set oXcl = CreateObject("Excel.Application")
    oXcl.Workbooks.Open Filename:=sEventSetterPath, ReadOnly:=False
    oXcl.Visible = True
    Set oXlEvents.appWithEvents = Nothing
    Select Case lCaseNum
      Case Is = 1
        AppActivate Application.Caption
      Case Is = 2
        AppActivate oXcl.Caption
    End Select
    wbEventSet.Close False
  Else
    Call Continue_Open
  End If
End Sub

Sub Continue_Open()
  'Code with special settings and procedures required for the workbook 
End Sub

Sub UndoSettings()
  'Code to "undo" any special settings when workbook opened   
 End Sub