Iterating unregistered add-ins (.xla)

add-in, excel, excel-addins, vba

Solution

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

Problem

I need help in - 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`. Demonstrating the issue: 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 ``` 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") ``` 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?

Original source