How to create a Reference with VBA
The easiest way is to set the reference in Excel and then query the
reference for its GUID value.
This is some code posted recently by RB Smisseart that list data on the
references
A GUID should be unique and consistent for an application across versions.
The registry will have one version of the application mapped to application.
So if you have multiple versions, it is usually mapped to the last
installed. You can look in the registry to find these entries (but for a
small population, setting them in Excel and then reading them is probably
easiest).
---- code from Bart -------
Will dump all the references of all open projects:
Sub ListExcelReferences()
'to list all the references in Excel
'-----------------------------------
Dim i As Long
Dim n As Long
Dim iRefCount As Long
Dim VBProj As Object
Cells.Clear
Cells(1).Value = "Project name"
Cells(2).Value = "Project file"
Cells(3).Value = "Reference Name"
Cells(4).Value = "Description"
Cells(5).Value = "FullPath"
Cells(6).Value = "GUID"
Cells(7).Value = "Major"
Cells(8).Value = "Minor"
On Error Resume Next 'as an un-saved workbook has no filename yet
For Each VBProj In Application.VBE.VBProjects
n = n + 1
With VBProj
iRefCount = .References.Count
With .References
For i = 1 To iRefCount
n = n + 1
If i = 1 Then
Cells(n, 1).Value = VBProj.Name
Cells(n, 2).Value = VBProj.Filename
If Err.Number = 76 Then 'Path not found
Cells(n, 2).Value = "Project not saved yet"
Err.Clear
End If
End If
Cells(n, 3).Value = .Item(i).Name
Cells(n, 4).Value = .Item(i).Description
Cells(n, 5).Value = .Item(i).FullPath
Cells(n, 6).Value = .Item(i).GUID
Cells(n, 7).Value = .Item(i).Major
Cells(n, 8).Value = .Item(i).Minor
Next i
End With
End With
Next
On Error GoTo 0
ThinRightBorder Range(Cells(2), Cells(n, 2))
Range(Cells(1), Cells(8)).Font.Bold = True
MediumBorder Range(Cells(1), Cells(8))
Range(Cells(1), Cells(n, 8)).Columns.AutoFit
End Sub
Sub MediumBorder(rng As Range, Optional wsh As Worksheet)
'puts a medium border around the passed range
'--------------------------------------------
Dim Sh As Worksheet
If wsh Is Nothing Then
Set Sh = ActiveWorkbook.ActiveSheet
Else
Set Sh = wsh
End If
With Sh.Range(rng.Address)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With
End Sub
Sub ThinRightBorder(rng As Range)
With rng
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub
--
Regards,
Tom Ogilvy
"Patrick" wrote in message
...
I just got really excited here... When trying to send spreadsheets through
different versions of Lotus Notes or working with different versions of
our
3270 emulator I run into problems with compile errors because some of the
users' machines do not have the references they need. With that in mind I
have two questions:
1) How can I identify the global unique ID value of the reference objects?
2) Could I have some code that would test each machine for different
reference IDs and apply the proper one? (if so, a small sample would be
appreciated)
Thanks for the epiphany, Tom!!!
patrick
"Tom Ogilvy" wrote:
for word it would be:
Dim s as String
s = "{00020905-0000-0000-C000-000000000046}"
Thisworkbook.VBProject.References.AddFromGuid s,0,0
--
Regards,
Tom Ogilvy
"Gérard Ducouret" wrote:
Hello,
How can I create a Reference (Such as Tools / Reference in a VBA
module) to
Microsoft Word 9.0 or to Microsoft Word 11.0, with VBA, then remove
the
reference at the end of the procedure?
Thanks ahead!
Gérard
|