Add references programaticly ?

[Patrick Bielen]

Hi All,

I was wondering if it is possible to add a reference
programaticly ?

The reason i ask is this...
Guess you get a workbook from a customer,
but it does not includes macro's aka VBA-code.
So you created a module that parses database-tabels,
and formats the sheets, and it should be run automaticly
when openeng the workbook (Autpen method).

So i was wondering if your could to the referencing
programaticly, like for example MS activeX component
referencing to use ado (ole db) drivers to connect to
the database.

If if it can be done, the user should simply import the
module, save the workbook, close and reopen again
to let the workbook do its needed stuff.

Is this possible ?

Best Regards,

Patrick
Microsoft Certified Professional

 

[RB Smissaert]

Here an example to set a reference to the Word library.


Sub ActivateWordLibrary()

Dim r

On Error Resume Next

'no need to carry on if the Word Object Library is already there
'---------------------------------------------------------------
For Each r In ThisWorkbook.VBProject.References
If r.GUID = "{00020905-0000-0000-C000-000000000046}" Then
Exit Sub
End If
Next

'Will this work with any Word version from 97 upwards?
'-----------------------------------------------------
'hopefully it will by doing Major:=0, Minor:=0
'Word 2002 is Major:=8, Minor:=2
'Word 2003 is Major:=8, Minor:=3
'-----------------------------------------------------
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{00020905-0000-0000-C000-000000000046}", _
Major:=0, Minor:=0

On Error GoTo 0

End Sub

Here a Sub to get the GUID.
This works from Excel and you may have to alter if it doesn't run in that.

Sub GetLibraryGUID()

Dim c As Byte
Dim myCheck As Long
Dim P As Boolean
Dim Rng As Range
Dim i As Byte

c = ActiveWorkbook.VBProject.References.Count

On Error Resume Next
Dim Message, Title, Default, T As Single
Message = "NUMBER ?" & Chr(13) & "________"
Title = " GET REFERENCES GUID ( 1 TO " & c & " )"
Default = c
T = InputBox(Message, Title, Default, 3500, 3500)

If Not T Mod 1 = 0 Then
Exit Sub
End If

If T < 1 Or T > c Then
Exit Sub
End If

MsgBox "REFERENCE ( " & T & " ) NAME : " & _
ActiveWorkbook.VBProject.References(T).Name & vbCrLf & vbCrLf & _
"MAJOR : " & _
ActiveWorkbook.VBProject.References.Item(T).Major & _
vbCrLf & vbCrLf & "MINOR : " & _
ActiveWorkbook.VBProject.References.Item(T).Minor & _
vbCrLf & vbCrLf & _
"GUID ( " & T & " ) : " & _
ActiveWorkbook.VBProject.References.Item(T).GUID, , _
" REFERENCES GUID : ITEM " & T

myCheck = MsgBox(" PUT INFORMATION IN SHEET ?", _
vbYesNo, " GetLibraryGUID")

If myCheck = vbNo Then
Exit Sub
End If

If ActiveSheet.ProtectContents = True Then
P = True
ActiveSheet.Unprotect
Else
P = False
End If

Range(Cells(ActiveCell.Row, ActiveCell.Column), _
Cells(ActiveCell.Row + 3, ActiveCell.Column + 1)).Select

For Each Rng In Selection.Cells
If Not IsEmpty(Rng) Then
i = i + 1
End If
Next

If i > 0 Then
myCheck = MsgBox(" OVERWRITE DATA IN THIS RANGE ?", _
vbYesNo, " GetLibraryGUID")
If myCheck = vbNo Then
Exit Sub
End If
End If

On Error Resume Next
ActiveCell.Value = "NAME :"
ActiveCell.Offset(1, 0).Value = "MAJOR :"
ActiveCell.Offset(2, 0).Value = "MINOR :"
ActiveCell.Offset(3, 0).Value = "GUID :"
ActiveCell.Offset(0, 1).Value = _
ActiveWorkbook.VBProject.References(T).Name
ActiveCell.Offset(1, 1).Value = _
ActiveWorkbook.VBProject.References.Item(T).Major
ActiveCell.Offset(2, 1).Value = _
ActiveWorkbook.VBProject.References.Item(T).Minor
ActiveCell.Offset(3, 1).Value = _
ActiveWorkbook.VBProject.References.Item(T).GUID

If P = True Then
ActiveSheet.Protect
End If

End Sub


RBS