How to list ALL references available in Excel VBE? 1

[socomfort]

Hello experts,

I was wondering if it is possible to list all object library references in MS Excel VBE References Dialog Box. I know that the code below will print those references in the ActiveProject (the ones that are checked in the dialog).

Function ListAllRefs()

     For Each my_ref In ThisWorkbook.VBProject.References

         Debug.Print my_ref.Name
     Next

End Function

The reason I want to do this is so that I can capture Reference:
1. Name
2. Description
3. GUID

I can return the TypeLibrary and Path and I can also search for broken references, but I'd like to dynamically capture the information above too.

In this way, I can AddFromGUID by passing string arguments to a function or I can also AddFromFile.

I appreciate your help.

thanks,

Ben

 

[combo]

After referencing 'Microsoft Visual Basic for Applications Extensibility 5.3' you can:

Function ListAllRefs()
Dim my_ref As VBIDE.Reference
     For Each my_ref In ThisWorkbook.VBProject.References
         With my_ref
            Debug.Print .Name, .Description, .FullPath, .IsBroken
            Debug.Print
        End With
     Next
End Function

The code checks references for the workbook with running code, it can be replaced by any open one, not necessarily active. However, its vba project shouldn't be locked and general security settings should allow programmatic access to vba project.

combo

 

[socomfort]

Hi combo,

Thanks for your reply. Your code does return the properties for the checked (selected) references. I'm trying to figure out how to return this information for the refs that ARE NOT checked (not selected in the dialog box).

Here are the NAMES of the refs that are currently selected in my instance of Excel:

VBA
Excel
stdole
Office
VBIDE

But as an example, the question now is: How can I return reference information for JRO (Microsoft Jet and Replication Objects 2.6 Library), which is NOT checked in the VBE References Dialog Box?

Perhaps I need to search the system registry for the registration information?

I'm stumped for now. Thanks again for your help.



-Ben

 

[combo]

Guess that you don't try to duplicate the 'References' dialog and need to test only specific library.
In this case you can:
- reference it and test the reference on another machine,
- try to add the reference and test it,
- test with CreateObject function,
- search registry for specific GUID (HKEY_CLASSES_ROOT).
Without reference set, you use late binding, so the CreateObject seems to be simplest.

Note that the list is dynamic - if you open a new excel file, its vba project becomes available to reference it. Sometimes the 'available reference' occurs unavailable when you try to reference it.

combo

 

[socomfort]

Thanks combo! That's just what I needed. Each reference that is listed in the VBE Dialog Box is registered. Therefore, I can find the Type Library information within each CLSID in HKEY_SYSTEM_ROOT.

thanks again dude,



Ben