Add Reference

[Zack Barresse]

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]

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]

Great! Thanks Mike!! :)

[mvidas]

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]

Doh! I always do that. Matt! Thank you! (sorry 'bout that, btw, I'ma dork.)