Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to cylce through all the open .xla's as well as the .xls'
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to cylce through all the open .xla's as well as the .xls'
Lloyd,
Use the AddIns collection ? Check if each is .Installed, if so, do what you need. NickHK "pinkfloydfan" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to cylce through all the open .xla's as well as the .xls'
Thanks Nick
That sounds good, just didn't know that it existed! Cheers Lloyd |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to cylce through all the open .xla's as well as the .xls'
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to cylce through all the open .xla's as well as the .xls'
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" wrote in message ups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to cylce through all the open .xla's as well as the .xls'
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to cylce through all the open .xla's as well as the .xls'
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 "pinkfloydfan" wrote in message ups.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to cylce through all the open .xla's as well as the .xls'
Also include -
' If adn.Installed Then Set vbp = Nothing ' End if at the beginning or end of the If...End If Peter T "Peter T" <peter_t@discussions wrote in message ... 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 "pinkfloydfan" wrote in message ups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to cylce through all the open .xla's as well as the .xls'
thanks everyone for your kind assistance
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do you want to save the changes you made to 'ABC.xls'? | Excel Discussion (Misc queries) | |||
Saving variables in XLA's | Excel Programming |