迭代未注册的加载项（.xla）

本文介绍了迭代未注册的加载项（.xla）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要帮助

• 弄清楚如何遍历当前打开的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