References 2

[lchase]

Can you use code to detect access 2000 and xp and then code
a reference to the correct excel library 9 or 10? If so can you also remove a missing reference?
Thanks
Len

 

[LittleSmudge]

The following code just happens to be on my code window at the moment as I flitted to tecTips while I waited for the form to save.
I think it covers the second part of what you want

For the form that auto opens when the database opens

Private Sub Form_Open()
' VBA c:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
' Access c:\Program Files\Microsoft Office\Office\MSACC9.OLB
' stdole c:\windows\SYSTEM\StdOle2.Tlb
' ADODB C:\PROGRAM FILES\COMMON FILES\SYSTEM\ADO\msado21.tlb
' ADOX C:\PROGRAM FILES\COMMON FILES\SYSTEM\ADO\MSADOX.DLL
' ADOR C:\PROGRAM FILES\COMMON FILES\SYSTEM\ADO\MSADOR15.DLL

If Not CheckSetRef("VBA", "c:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL") Then
    MsgBox "VBA library reference failed"
ElseIf Not CheckSetRef("Access", "c:\Program Files\Microsoft Office\Office\MSACC9.OLB") Then
    MsgBox "Access library reference failed"
ElseIf Not CheckSetRef("stdole", "c:\windows\SYSTEM\StdOle2.Tlb") Then
    MsgBox "stdole library reference failed"
ElseIf Not CheckSetRef("ADODB", "C:\PROGRAM FILES\COMMON FILES\SYSTEM\ADO\msado21.tlb") Then
    MsgBox "ADODB library reference failed"
ElseIf Not CheckSetRef("ADOX", "C:\PROGRAM FILES\COMMON FILES\SYSTEM\ADO\MSADOX.DLL") Then
    MsgBox "ADOX library reference failed"
ElseIf Not CheckSetRef("ADOR", "C:\PROGRAM FILES\COMMON FILES\SYSTEM\ADO\MSADOR15.DLL") Then
    MsgBox "ADOR library reference failed"
Else
    Exit Sub
End If
Domd.Quit
End Sub

In a Global Module - you'll need

Public Function CheckSetRef(RefName As String, RefPath As String) As Boolean
'###################################################################'
' Checks to see if a Refernce exist that matches RefName            '
' AND check that the link is not broken.                            '
' If the check fails then it sets up a link using RefPath           '
'                                                                   '
' Returns True if check turned out Okay or if  link was successful  '
'###################################################################'
    
    Dim ref As Reference
    
    CheckSetRef = False
    For Each ref In Application.References
        If ref.Name = RefName _
         And ref.IsBroken = False _
         Then CheckSetRef = True
    Next
    
    If CheckSetRef Then
        ' Link to Reference library exists so make no change
    Else
        CheckSetRef = ReferenceFromFile(RefPath)
    End If

End Function

Private Function ReferenceFromFile(strFileName As String) As Boolean
'###################################################################'
' Used by Function above to do the establishing of the Reference    '
'###################################################################'

    Dim ref As Reference

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

Exit_ReferenceFromFile:
Exit Function

Error_ReferenceFromFile:
If Err.Number = 32813 Then
    Resume Next
Else
    MsgBox Err & ": " & Err.Description, , Err.Number
    ReferenceFromFile = False
    Resume Exit_ReferenceFromFile
End If
End Function

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[mpopnoe]

How do you remove a reference in code?

I can't seem to use the remove method properly:

Dim objRef As Object, strDesc As String
For Each objRef In ThisWorkbook.VBProject.References
If InStr(objRef.Name, &quot;EDD_TEMPLATE&quot <> 0 Then
'objRef.Remove
Application.VBE.VBProjects _(&quot;TEC_CLN.XLM&quot.References.Remove (objRef)

End If
Next objRef

** I'm not sure how to pass the reference as Reference to the remove method.

 

[VBslammer]

This works for me, although ironically, you have to set a reference to the 'Microsoft Visual Basic for Applications Extensiblitiy 5.3' library in order to use an explicit declaration for 'Reference' and 'References.'

Sub removeRef()
  Dim ref As Reference
  Dim refs As References
  Set refs = ActiveWorkbook.VBProject.References
  
  For Each ref In refs
    If ref.Description = "Microsoft Browser Helpers" Then
      refs.Remove ref
    End If
  Next ref
  
End Sub

VBSlammer

Unemployed in Houston, Texas