vba 尝试在单独的实例中打开工作簿

Trying to open the workbook in separate instance

提问by user2457968

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.

我正在尝试在新实例中打开一本工作簿。但有些地方它不能正常工作。下面是代码供您参考。我正在尝试在新实例中打开名为“Loginfrm”的表单。

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

采纳答案by user2457968

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


1) Create a new workbook
2) Open the VBE(Visual Basic Editor) - ALT + F11
3) Insert new UserFormand Module(right click in theproject explorerthen 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

我将向您展示如何在另一个 excel 实例中运行宏，在您的情况下将显示一个1) 创建一个新工作簿2) 打开VBE( Visual Basic 编辑器) - 3) 插入新的和（右键单击然后是项目浏览器）。您的屏幕应与下图类似： 4)为注释添加引用：我的代码中已包含此内容，但您必须确保已正确附加它UserForm1



ALT + F11
UserFormModuleInsert



Microsoft Visual Basic for Applications Extensibility 5.3

5) In the newly created Module1insert the code

5）在新创建的Module1插入代码

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 MainMacro and you will be shown the Userform1

6) 现在，运行Main宏，您将看到Userform1

回答by Dave

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:

我只是一个新手，但这对我有用。此代码似乎在 Excel 的新实例中打开您的文件。将此 vba 代码复制并粘贴到 ThisWorkBook 对象中：

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