迭代未注册的加载项（.xla） [英] Iterating unregistered add-ins (.xla)

问题描述

我需要帮助

• 弄清楚如何遍历当前打开的Excel加载项文件（。xla）尚未使用工具>在Excel中注册的。加载项菜单路径。


• 更具体地说，我对任何未出现在加载项对话框中但具有<$ c的工作簿感兴趣$ c> 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）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！