PDA

View Full Version : Solved: Add Reference



Zack Barresse
08-19-2004, 10:11 AM
Hi,

I'd like to be able to Add a reference in VBA (if not referenced already). Here Dennis lays out how to make a reference using code:

http://www.excelkb.com/article.aspx?id=10076

This is what I'd like to do, but I need it for another reference (the Outlook Object Library). I think I could probably get it, but how in the world do you get the GUID number?

mvidas
08-19-2004, 10:22 AM
I asked this on EE not too long ago, and this is the response I got from Dave/Brettdj:


And for a handy Guid reference courtesy of Denis W for any other refs you may want to add

Option Explicit

Sub List_ActiveReferences_VBAProject()
'Intentionally use of late binding but if we want to
'use early binding then we set a reference to the
'"Microsoft Visual Basic for Applications Extensibility 5.3" for 2000 and above
'For Excel 97 the name is "Microsoft Visual Basic for Applications Extensibility"

Dim oVBReference As Object
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim i As Long

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")

Application.ScreenUpdating = False

With wsSheet
.Range("A1:F1").Value = _
Array("Description", "Name", "GUID", "Major", "Minor", "Path")
For Each oVBReference In wbBook.VBProject.References
i = i + 1
.Cells(i, 1).Value = oVBReference.Description
.Cells(i, 2).Value = oVBReference.Name
.Cells(i, 3).Value = oVBReference.GUID
.Cells(i, 4).Value = oVBReference.Major
.Cells(i, 5).Value = oVBReference.Minor
.Cells(i, 6).Value = oVBReference.Fullpath
Next oVBReference
.Columns("A:F").EntireColumn.AutoFit
End With

Application.ScreenUpdating = True

Set oVBReference = Nothing
End Sub

Zack Barresse
08-19-2004, 10:23 AM
Great! Thanks Mike!! :)

mvidas
08-19-2004, 10:25 AM
You can thank Dave :) You can also see the question I asked at http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21072688.html .. Jeroen gave a good example on how to check excel version before adding the reference (could make a huge difference).

<< also, it's Matt :) >>

Matt

Zack Barresse
08-19-2004, 10:35 AM
Doh! I always do that. Matt! Thank you! (sorry 'bout that, btw, I'ma dork.)