尝试单独打开工作簿 [英] Trying to open the workbook in separate instance

问题描述

不知道我是否正确请指教我。

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

我试图在新的实例中打开一个工作簿。但有些地方不正常。以下是代码供您参考。我试图在新实例中打开名为Loginfrm的表单。

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

推荐答案

我要去显示如何在另一个excel 实例中运行宏，在这种情况下，您将会显示一个 UserForm1





1）创建新工作簿

2）打开 VBE （ Visual Basic编辑器） - ALT + F11

3）插入新的 UserForm 和模块（右键单击 项目资源管理器 然后插入 ）。您的屏幕应该类似于以下图片：






4）添加 Microsoft Visual Basic应用程序可扩展性5.3的引用


注意：我已经在我的代码中，但是你必须确保您已正确附加它

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）在新创建的 Module1 插入代码

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）现在，运行 Main 宏，您将显示 Us erform1

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

这篇关于尝试单独打开工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！