excel - 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

Answer 1

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:



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 FilesMicrosoft SharedVBAVBA6VBE6EXT.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