CODEWITHSUNDEEP
excelvba
Sam
Sam

Reputation: 25

Is there a way to create an "Orphan" Excel instance for a specific workbook?

Situation: Currently, I am able to create a new instance of Excel, assign the current open workbook to that instance and close the old instance; however, when opening other Excel workbooks, they are all being assigned to the new instance I created.

Goal/Reason: I am seeking to understand how I can prevent other workbooks from being opened in the new instance. The reason for this need is that there are certain edits that I would like to make in order to only affect one instance of Excel and not the others, hence the term "Orphan" instance.

Update: I did learn that if I do not include the code to quit the application, then any workbooks I open are assigned to the old instance. This was the closest I could get to my goal; however in this situation I would need to remove that old instance.

Sub SaveCloseReOpen()

Dim xlApp As Excel.Application

Set xlApp = New Excel.Application              'Create a new instance of Excel.

xlApp.Workbooks.Open ThisWorkbook.FullName     'Open the current workbook in that instance.

xlApp.displayStatusBar = False                 'Hide the status bar of the new instance.
xlApp.displayFormulaBar = False                'Hide the formula bar of the new instance.

xlApp.Visible = True                           'Display the instance.
Set xlApp = Nothing                            'Set variable to nothing.

Application.DisplayAlerts = False              'Turn off alerts.
ThisWorkbook.Save                              'Save current workbook.
Application.DisplayAlerts = True               'Turn on alerts.
Application.Quit                               'Close previous instance application.

End Sub

Upvotes: 1

Views: 117

Answers (1)

VBasic2008
VBasic2008

Reputation: 54883

Change Application Properties When Activating a Workbook

NEW

  • It's kind of a madhouse, but maybe you will find it useful.
  • It will create the VBScript file SCRO.vbs in the workbooks folder, to be run to open the workbook in another instance. Then it will close the workbook in this instance.
  • Additionally still use the Workbook_Deactivate procedure to not end up with invisible bars.
  • I would personally stick with the first solution since I still don't see any benefits of the new instance.
  • If this doesn't work or gives you any trouble, your feedback will be most appreciated since it's kind of out of my league (there may be consequences I'm not aware of).
Option Explicit

Sub SaveCloseReOpen()
    Dim FilePath As String: FilePath = ThisWorkbook.FullName
    Dim vbsPath As String: vbsPath = ThisWorkbook.Path & "\" & "SCRO.vbs"
    Dim vbsContents As String: vbsContents = vbsOpenFileInNewInstance(FilePath)
    writeToTextFile vbsPath, vbsContents
    CreateObject("Wscript.Shell").Run vbsPath
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    ThisWorkbook.Close
    Application.DisplayAlerts = True
End Sub
    
Function vbsOpenFileInNewInstance( _
    ByVal FilePath As String) _
As String
    Dim S As String
    S = S & "openFileInNewInstance """ & FilePath & """" & vbLf
    S = S & "Sub openFileInNewInstance(FilePath)" & vbLf
    S = S & "    With CreateObject(""Excel.Application"")" & vbLf
    S = S & "        .Visible = True" & vbLf
    S = S & "        .Workbooks.Open FilePath" & vbLf
    S = S & "        .DisplayStatusBar = False" & vbLf
    S = S & "        .DisplayFormulaBar = False" & vbLf
    S = S & "        Exit Sub" & vbLf
    S = S & "    End With" & vbLf
    S = S & "End Sub"
    vbsOpenFileInNewInstance = S
    Debug.Print S
End Function

Sub writeToTextFile( _
        ByVal FilePath As String, _
        ByVal WriteString As String)
    Dim Num As Long: Num = FreeFile()
    Open FilePath For Output As #Num
    Print #Num, WriteString
    Close #Num
End Sub

OLD

  • You could use the workbook events.
  • Copy the following code into the ThisWorkbook module.
Option Explicit

Private Sub Workbook_Activate()
    Application.DisplayStatusBar = False
    Application.DisplayFormulaBar = False
End Sub

Private Sub Workbook_Deactivate()
    Application.DisplayStatusBar = True
    Application.DisplayFormulaBar = True
End Sub

Upvotes: 1

Related Questions