Enumerate reference GUID's

[stanl]

I'm looking for a quick way to build a lookup table for all avaialble references in Excel:

example:
Reference_Name GUID Major Minor
VBA Extensibility {0002E157-0000-0000-C000-000000000046} 5 3

Stan

[Emily]

Code to list reference for active WorkBook's reference

You can select ALL references ...


[vba]
Sub Grab_References()
Dim n As Integer
Dim x As Integer
On Error Resume Next
x = 1
For n = 1 To ActiveWorkbook.VBProject.References.Count
Cells(x, 1) = n
Cells(x, 2) = ActiveWorkbook.VBProject.References.Item(n).Name
Cells(x, 3) = ActiveWorkbook.VBProject.References.Item(n).Description
Cells(x, 4) = ActiveWorkbook.VBProject.References.Item(n).Major
Cells(x, 5) = ActiveWorkbook.VBProject.References.Item(n).Minor
Cells(x, 6) = ActiveWorkbook.VBProject.References.Item(n).fullpath
Cells(x, 7) = ActiveWorkbook.VBProject.References.Item(n).GUID
x = x + 1
Next n
Columns("A:G").EntireColumn.AutoFit
End Sub

[/vba]

'
List all checked GUIDS
http://vbaexpress.com/kb/getarticle.php?kb_id=713

[stanl]

Emily,

That code gives all checked references. In review, I see I wasn't clear when I said all available references, this would refer to all checked/unchecked. I was thinking I could run a WMI query for ProgID's, but I am not sure I get the correct major and minor.

The point is to build a lookup table to be used when programmatically inserting code into newly created Workbooks. For example, assume I wanted to create a new workbook and insert a module that used ADOX - and this workbook might be sent to another user, I would need to determine if a reference to Microsoft ADO Ext. 2.8 for DDL and Security

existed and set it using .addfromGuid() if necessary.

In theory, the lookup could build as various machines might have references not in the initial list. Also, if a reference in the lookup could not be added on a machine (as the ProgID does not exist in the registry) then the VBA code would not even try to execute.

Hope this is clearer. Stan

[Bob Phillips]

I don't think that there is any way to get an unchecked references.

[Emily]

Emily,

That code gives all checked references. In review, I see I wasn't clear when I said all available references, this would refer to all checked/unchecked.

You can select ALL references ...

[stanl]

I don't think that there is any way to get an unchecked references.

I know... that's an Excel Mystery... I keep thinking it's gotta be stored somewhere, anyway, that is why I mentioned the WMI route, easpecially for custom OCX's you might register and add. ITMT: I trust you appreciate the goal here. Stan

[johnske]

...and this workbook might be sent to another user, I would need to determine if a reference to Microsoft ADO Ext. 2.8 for DDL and Security

existed and set it using .addfromGuid() if necessary.

In theory, the lookup could build as various machines might have references not in the initial list. Also, if a reference in the lookup could not be added on a machine (as the ProgID does not exist in the registry) then the VBA code would not even try to execute...

just use code to set your reference and add error-handling to tell you if it's not registered on their machine. If not registered, your code could be terminated there...

Example
[vba]
Option Explicit

Private Sub AddReference()

Dim Reference As Object

On Error GoTo ErrMsg
With ThisWorkbook.VBProject
For Each Reference In .References
If Reference.Description Like "Microsoft Forms 12.0 Object Library" Then Exit Sub
Next
.References.AddFromGuid "{0D452EE1-E08F-101A-852E-02608C4D0BB4}", 12, 0
End With
Exit Sub

ErrMsg:
MsgBox "Object Library Not Registered on this machine"
End Sub
[/vba]

[stanl]

You can select ALL references ...

tedious, and you may appropriate links that are broken, or reference multiple versions of ADO which may screw stuff up [don't ask me why or how, but I have witnessed it]. If I can get the WMI correct, I'll post it.

Thanx

Stan