Iterating unregistered add-ins (.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 AddInDim w As WorkbookOn Error Resume NextWith 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    NextEnd With

I have had issues with addins that are installed (and in the VBE) not being available via user's Addin on Exel 2013 (in a work environment).

Tinkering with the solution from Chris C gave a good workaround.

Dim a As AddInDim wb As WorkbookOn Error Resume NextWith Application    .DisplayAlerts = False        For Each a In .AddIns2        Debug.Print a.Name, a.Installed            If LCase(Right$(a.Name, 4)) = ".xla" Or LCase(Right$(a.Name, 5)) Like ".xla*" Then                Set wb = Nothing                Set wb = .Workbooks(a.Name)                wb.Close False                Set wb = .Workbooks.Open(a.FullName)            End If        Next   .DisplayAlerts = TrueEnd With

I'm still on the lookout for a sane solution for this problem, but for the time being it seems that reading the window texts of all workbook windows gives a collection of all open workbooks, add-in or not:

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPrivate Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As LongPrivate Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As LongPublic Function GetAllOpenWorkbooks() As Collection'Retrieves a collection of all open workbooks and add-ins.Const EXCEL_APPLICATION_WINDOW  As String = "XLDESK"Const EXCEL_WORKBOOK_WINDOW     As String = "EXCEL7"Dim hWnd                As LongDim hWndExcel           As LongDim contentLength       As LongDim buffer              As StringDim bookName            As StringDim books               As CollectionSet books = New Collection'Find the main Excel windowhWndExcel = FindWindowEx(Application.hWnd, 0&, EXCEL_APPLICATION_WINDOW, vbNullString)Do    'Find next window    hWnd = FindWindowEx(hWndExcel, hWnd, vbNullString, vbNullString)    If hWnd Then        'Create a string buffer for 100 chars        buffer = String$(100, Chr$(0))        'Get the window class name        contentLength = GetClassName(hWnd, buffer, 100)        'If the window found is a workbook window        If Left$(buffer, contentLength) = EXCEL_WORKBOOK_WINDOW Then            'Recreate the buffer            buffer = String$(100, Chr$(0))            'Get the window text            contentLength = GetWindowText(hWnd, buffer, 100)            'If the window text was returned, get the workbook and add it to the collection            If contentLength Then                bookName = Left$(buffer, contentLength)                books.Add Excel.Application.Workbooks(bookName), bookName            End If        End If    End IfLoop While hWnd'Return the collectionSet GetAllOpenWorkbooks = booksEnd Function