Reference Detection - Can It Be Done? 1

[GGleason]

Within a module, I want to be able to detect if the Microsoft Excel 8.0 Object Library (EXCEL8.OLB) is checked as a reference. Then, if it is not checked, to be able activate it within the module.

Is this even possible?

tia,
GGleason

 

[cmmrfrds]

This is out of Access Help. Although, I haven't done it, it sure looks like this will work.
Reference Object, References Collection Example

The following example creates a reference to a specified type library:

Function ReferenceFromFile(strFileName As String) As Boolean
Dim ref As Reference

On Error GoTo Error_ReferenceFromFile
Set ref = References.AddFromFile(strFileName)
ReferenceFromFile = True

Exit_ReferenceFromFile:
Exit Function

Error_ReferenceFromFile:
MsgBox Err & ": " & Err.Description
ReferenceFromFile = False
Resume Exit_ReferenceFromFile
End Function
You could call this function by using a procedure such as the following, which creates a reference to the calendar control:

Sub CreateCalendarReference()
If ReferenceFromFile("C:\Windows\System\Mscal.ocx&quot = True Then
MsgBox "Reference set successfully."
Else
MsgBox "Reference not set successfully."
End If
End Sub

 

[GGleason]

Thanks. I should have looked in the help, but I thought this was so esoteric that I didn’t even bother.

The good news is that the code worked but I modified it to suit my needs.

Here is the modified function:

Function ReferenceFromFile(strFileName As String, strRefName As String) As Boolean
Dim ref As Reference, i As Integer

On Error GoTo Error_ReferenceFromFile

For i = 1 To References.Count
If References.Item(i).Name = strRefName Then
ReferenceFromFile = True
Exit Function
End If
Next i

Set ref = References.AddFromFile(strFileName)
ReferenceFromFile = True

Exit_ReferenceFromFile:
Exit Function

Error_ReferenceFromFile:
MsgBox Err & ": " & Err.Description
ReferenceFromFile = False
Resume Exit_ReferenceFromFile
End Function

Here is the revised code that calls the function:

If ReferenceFromFile("C:\Program Files\Microsoft Office\Office\EXCEL8.OLB", "Excel&quot = True Then
' MsgBox "Reference set successfully."
Else
MsgBox "Excel reference not set successfully!", vbExclamation
End If

This helps a lot!

Many thanks,
GGleason