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?
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:
As of Office 2010, there is a new collection .AddIns2 which is the same as .AddIns but also includes the unregistered .XLA plug-ins.
What about this:
Any use?
Is iterating through the registry a possibility? I know that that doesn't give you a snapshot of what your instance of Excel is using, but what a new instance would use - but depending on what you need it for, it might be good enough.
The relevant keys are:
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.
Use =DOCUMENTS, an Excel4 macro function.
Here's the documentation for it (available here):
DOCUMENTS
Returns, as a horizontal array in text form, the names of the specified open workbooks in alphabetic order. Use DOCUMENTS to retrieve the names of open workbooks to use in other functions that manipulate open workbooks.
Syntax
DOCUMENTS(type_num, match_text)
Type_num is a number specifying whether to include add-in workbooks in the array of workbooks, according to the following table.
Match_text specifies the workbooks whose names you want returned and can include wildcard characters. If match_text is omitted, DOCUMENTS returns the names of all open workbooks.