我需要帮助
- 弄清楚如何遍历当前打开的Excel加载项文件(。xla)尚未使用工具>在Excel中注册的。加载项菜单路径。
- 更具体地说,我对任何未出现在加载项对话框中但具有 ThisWorkbook.IsAddin = True 。
- figuring out how to iterate through currently open Excel add-in files (.xla) that have not been registered in Excel using the Tools > Add-ins menu path.
- more specifically, I am interested in any workbook that doesn't appear in the Add-In dialog, but has ThisWorkbook.IsAddin = True.
演示问题:
尝试如下遍历工作簿不会获得的工作簿。AddIn= True :
Trying to loop through workbooks as follows doesn't get workbooks with .AddIn = True:
Dim book As Excel.Workbook For Each book In Application.Workbooks Debug.Print book.Name Next book通过加载项循环不会获得未注册的加载项:
Looping through add-ins doesn't get add-ins that are not registered:
Dim addin As Excel.AddIn For Each addin In Application.AddIns Debug.Print addin.Name Next addin在VBProjects集合中循环有效,但前提是用户特别信任可以在宏安全性设置中访问Visual Basic项目-很少:
Looping through the VBProjects collection works, but only if user has specifically trusted access to the Visual Basic Project in the Macro Security settings - which is rarely:
Dim vbproj As Object For Each vbproj In Application.VBE.VBProjects Debug.Print vbproj.Filename Next vbproj但是,如果知道工作簿的名称,则可以直接引用该工作簿,而不管它是否是外接程序:
However, if the name of the workbook is known, the workbook can be referenced directly regardless of whether it is an add-in or not:
Dim book As Excel.Workbook Set book = Application.Workbooks("add-in.xla")但是,如果名称未知,并且不能依靠用户的宏安全设置,如何获得对该工作簿的引用呢? / p>
But how the heck to get reference to this workbook if the name is not known, and user's macro security settings cannot be relied on?
推荐答案从Office 2010开始,有一个新集合.AddIns2与.AddIns相同,但还包括未注册的.XLA
As of Office 2010, there is a new collection .AddIns2 which is the same as .AddIns but also includes the unregistered .XLA plug-ins.
Dim a As AddIn Dim w As Workbook On Error Resume Next With Application For Each a In .AddIns2 If LCase(Right(a.name, 4)) = ".xla" Then Set w = Nothing Set w = .Workbooks(a.name) If w Is Nothing Then Set w = .Workbooks.Open(a.FullName) End If End If Next End With更多推荐
迭代未注册的加载项(.xla)
发布评论