Trying to open the workbook in separate instance

Question

Not sure whether I am doing it right. Please advise me.

I am trying to open one workbook in new instance. But some where it is not working fine. Below is the code for your reference. I am trying to open the form named 'Loginfrm' in the new instance.

Suppose if another workbook is already open then the current code freezes that workbook also. Ideally this should not be happening.

Private Sub Workbook_Open()
Call New_Excel

Dim xlWrkBk As Excel.Workbook
Dim xlApp As New Excel.Application

Set xlWrkBk = xlApp.ActiveWorkbook
xlApp.Visible = True
'ThisWorkbook.Windows(1).Visible = False
LoginFrm.Show

End Sub
Sub New_Excel()
  'Create a Microsoft Excel instance via code
  'using late binding. (No references required)
  Dim xlApp As Object
  Dim wbExcel As Object

  'Create a new instance of Excel
  Set xlApp = CreateObject("Excel.Application")

  'Open workbook, or you may place here the
  'complete name and path of the file you want
  'to open upon the creation of the new instance
  Set wbExcel = xlApp.Workbooks.Add

  'Set the instance of Excel visible. (It's been hiding until now)
  xlApp.Visible = True

  'Release the workbook and application objects to free up memory
  Set wbExcel = Nothing
  Set xlApp = Nothing
End Sub

the form will always be shown in the instance in which its container workbook is open — JosieP, Jun 06 '13 at 08:41
@JosieP theres a little trick you can do:) let me give an answer — , Jun 06 '13 at 09:50
your example doesn't seem to use separate _instances of excel_ though — JosieP, Jun 06 '13 at 10:54
true but my comment still applies-you just copied the form over! :) — JosieP, Jun 07 '13 at 10:49

score 2 · Accepted Answer · edited May 23 '17 at 12:13

I am going to show you how to run a macro in another instance of excel ,which in your case will display a UserForm1

1) Create a new workbook
2) Open the VBE (Visual Basic Editor) - ALT + F11
3) Insert new UserForm and Module (right click in the project explorer then Insert). Your screen should look similar to the below picture:

step 3 overview

4) Add References for the Microsoft Visual Basic for Applications Extensibility 5.3
note: I have this already in my code, but you have to make sure you have properly attached it

5) In the newly created Module1 insert the code

Sub Main()
    AddReferences
    AddComponent "UserForm1", "UserForm1.frm"
End Sub

Private Sub AddReferences()
    '   Name:            VBIDE
    '   Description:     Microsoft Visual Basic for Applications Extensibility 5.3
    '   GUID:            {0002E157-0000-0000-C000-000000000046}
    '   Major:           5
    '   Minor:           3
    '   FullPath:        C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
    On Error Resume Next
    ThisWorkbook.VBProject.References.AddFromGuid GUID:="{0002E157-0000-0000-C000-000000000046}", _
                                                  Major:=5, Minor:=3
End Sub

Sub AddComponent(theComponent$, fileName$)

    ' export
    Application.VBE.ActiveVBProject.VBComponents(theComponent).Export ThisWorkbook.Path & "\" & fileName

    Dim xApp As Excel.Application
    Set xApp = New Excel.Application
    xApp.Visible = True

    Dim wb As Excel.Workbook
    Set wb = xApp.Workbooks.Add

    wb.VBProject.VBComponents.Import ThisWorkbook.Path & "\" & fileName

    CreateAModule wb
    xApp.Run "MacroToExecute"

    xApp.DisplayAlerts = False
    wb.Save
    wb.Close
    Set wb = Nothing
    xApp.Quit
    Set xApp = Nothing
    Application.DisplayAlerts = True
End Sub

Sub CreateAModule(ByRef wb As Workbook)

    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.vbComponent
    Dim CodeMod As VBIDE.CodeModule

    Set VBProj = wb.VBProject
    Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
    Set CodeMod = VBComp.CodeModule

    With CodeMod
        .DeleteLines 1, .CountOfLines
        .InsertLines 1, "Public Sub MacroToExecute()"
        .InsertLines 2, "    UserForm1.Show"
        .InsertLines 3, "End Sub"
    End With
End Sub

6) Now, run the Main Macro and you will be shown the Userform1

Yes, Josie is correct. It is not creating separate instance. However, the new instance will open in the code which I have given earlier. But, the file which I am trying to open will not open in the new instance. Please advise. — user2457968, Jun 07 '13 at 08:07

score 1 · Answer 2 · answered Dec 21 '13 at 02:29

I'm just a newbie, but this worked for me. This code appears to open your file in a new instance of Excel. Copy and paste this vba code into the ThisWorkBook object:

Option Explicit

Dim objExcel As Excel.Application

Dim FileName As String

Public Sub workbook_open()
    FileName = ThisWorkbook.FullName
    If vbReadOnly <> 0 Then
        Exit Sub
    Else
        objExcel.Workbooks.Open FileName:=FileName
        ThisWorkbook.Saved = True
        ThisWorkbook.Close
        objExcel.Quit
    End If
End Sub

Since Excel 2013 groups all new instances under the first running instance, this will unfortunately not work anymore. — yu_ominae, Jul 22 '14 at 02:06

score 0 · Answer 3 · answered Jan 03 '22 at 23:40

Microsoft Support - Command-line switches for Microsoft Office products - Excel

Using the Shell, you don't have to wait for the new instance of Excel to finish launching/opening the desired file.

Using the command line switch /x will open the file in a new instance of Excel.

If you have files in your XLSTART directory that you don't want opened again (the application displays ReadOnly prompt(s)), you can include the /s or /safemode switches to open in SafeMode.

Sub OpenFileInNewInstanceOfExcel(ByVal pathToFile as String)
    Call Shell("excel.exe /x """ & pathToFile & """"
End Sub

Trying to open the workbook in separate instance

3 Answers3

Linked