Iterating unregistered add-ins (.xla)

Question

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?

Answer 1

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