不确定我做得是否正确。请建议我。
我正在尝试在新实例中打开一本工作簿。但有些地方效果不太好。下面是代码供您参考。我正在尝试在新实例中打开名为“Loginfrm”的表单。
假设如果另一个工作簿已打开,则当前代码也会冻结该工作簿。理想情况下,这不应该发生。
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
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 https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically-vba-excel
note: I have this already in my code, but you have to make sure you have properly attached it
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) 现在,运行Main
宏,您将看到Userform1
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)