View Full Version : Enumerate reference GUID's
stanl
02-28-2007, 08:06 AM
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
02-28-2007, 08:41 AM
Code to list reference for active WorkBook's reference
You can select ALL references ... :banghead: :devil2:
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
'
List all checked GUIDS
http://vbaexpress.com/kb/getarticle.php?kb_id=713
stanl
02-28-2007, 11:54 AM
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.: pray2: Stan
Bob Phillips
02-28-2007, 12:46 PM
I don't think that there is any way to get an unchecked references.
Emily
02-28-2007, 02:26 PM
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 ... :banghead: :devil2:
stanl
02-28-2007, 03:08 PM
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
02-28-2007, 03:09 PM
...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
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
stanl
02-28-2007, 03:12 PM
You can select ALL references ... :banghead: :devil2:
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.