In the example -

ReDim arrXLA(1 To UBound(vasAddins))

would be better like this

ReDim arrXLA(1 To UBound(vasAddins)) As Workbook

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
Maybe the following will work for you, ie set references to all loaded
addins including any not in the addins collection.

If needs you could compare with the addins collection to determine if each
loaded addin exists in the collection, if it exists is it installed or if
exists but not installed it must have been loaded by other means. (BTW
compare using workbook.Title not .Name)

Sub test2()
dim i as Long
Dim s As String
Dim nm As Name
Dim vasAddins
Dim colAddins As AddIns

Set nm = ActiveWorkbook.Names.Add("myAddins", "=DOCUMENTS(2)")
' must be in a normal xls, so not thisworkbook if it's an addin

vasAddins = Application.Evaluate(nm.Name)

nm.Delete

If IsError(vasAddins) Or IsEmpty(vasAddins) Then
' no addins or method not successful
Exit Sub
End If

For i = 1 To UBound(vasAddins)
'remove any apostrophes
#If VBA6 Then
s = Replace(vasAddins(i), "'", "")
#Else
s = Application.Substitute(vasAddins(i), "'", "")
#End If
vasAddins(i) = s
Next

ReDim arrXLA(1 To UBound(vasAddins))
For i = LBound(vasAddins) To UBound(vasAddins)
Set arrXLA(i) = Workbooks(vasAddins(i))
Debug.Print arrXLA(i).FullName
Next

End Sub

Regards,
Peter T

"scotty" wrote in message
oups.com...

thank you for your responses.

the unfortunate part is i am writing code to discover what addins are
loaded and then peform various mergers etc. So, what i really needed
was to run through the workbooks collection, do a strcomp on xla to
pick them out.

it seems i need prior knowledge of their names as the collection loop
won't work.