I want to cylce through all the open .xla's as well as the .xls'

[pinkfloydfan]

Hi there

If you use something like this:

For Each mybook In Application.Workbooks

It will only reference all the open .xls files. According to the help
you can use Application.Workbooks("filename.xla") to access a
single .xla if you know its name but that doesn't help to write
generic code.

Does anyone know of a way to cycle through all the open .xla's without
having to know their names first?

Thanks a lot
Lloyd

 

[NickHK]

Lloyd,
Use the AddIns collection ?
Check if each is .Installed, if so, do what you need.

NickHK

 

[pinkfloydfan]

Thanks Nick

That sounds good, just didn't know that it existed!

Cheers
Lloyd

 

[pinkfloydfan]

Actually, that still leaves me with a problem

I want to be able to cycle through the VBComponents in each addin. If
I was looking at a Workbook then I would use:

For Each mybook In Application.Workbooks
For Each vbcomp In mybook.VBProject.VBComponents


But I can't see a way to access the VBProject property from the Addins
collection

Any ideas?

Thanks a lot
Lloyd

 

[NickHK]

Most addins that are not self written are password protected, so you would
not be able to access the components anyway.
Addins are not designed for this access in the Object model and AFAIK not
accessible., although I could well be wrong..

NickHK

 

[pinkfloydfan]

Thanks anyway Nick

Out of interest, after some searching I have found that MZTools will
generate XML documentation of procedures in both open .xla and .xls
files. This does what I was looking for.

http://www.mztools.com/v3/download.htm

All the best
Lloyd

 

[Peter T]

Sub test()
Dim adn As AddIn
Dim vbp As Object ' VBProject
Dim vbComp As Object ' VBComponent
Dim sName As String

For Each adn In Application.AddIns
If adn.Installed Then
sName = adn.Name
On Error Resume Next
Set vbp = Workbooks(adn.Name).VBProject
On Error GoTo 0
If vbp Is Nothing Then
Debug.Print sName & " project n/a" ' eg ANALYS32.XLL
ElseIf vbp.Protection = 1 Then 'vbext_pp_locked
Debug.Print sName & " locked"
Else ' vbext_pp_none
Debug.Print sName
For Each vbComp In vbp.VBComponents
Debug.Print , vbComp.Name
Next
End If
End If
Next
End Sub

Regards,
Peter T

 

[Peter T]

Also include -

' If adn.Installed Then
Set vbp = Nothing
' End if

at the beginning or end of the If...End If

Peter T

 

[pinkfloydfan]

thanks everyone for your kind assistance