VBA References (1 Viewer)

S

swingline

Member
Local time
Today, 03:41
Joined
Feb 18, 2020
Messages
51
I have an application that uses the Microsoft excel library reference. What's the best way to deal with different library versions installed on end users, computers?

For example, the version I have installed on my computer is 16; one user has 15, another has 14. How can I prevent the missing reference from breaking on load and auto-select the version that they have installed on their computer?
 
J

john_S

New member
Local time
Yesterday, 20:41
Joined
Jul 22, 2021
Messages
3
You can always auto add the reference

Code: 
Option Compare Database

Option Explicit

Function FixUpRefs()

    Dim loRef As Access.Reference

    Dim intCount As Integer

    Dim intX As Integer

    Dim blnBroke As Boolean

    Dim strPath As String

    Dim strVersion As String

  

    On Error Resume Next



    'Count the number of references in the database

    intCount = Access.References.Count

  

    'Loop through each reference in the database

    'and determine if the reference is broken.

    'If it is broken, remove the Reference and add it back.

    Debug.Print "----------------- References found -----------------------"

    Debug.Print " reference count = "; intCount



    For intX = intCount To 1 Step -1

      Set loRef = Access.References(intX)

      With loRef

        Debug.Print " reference = "; .FullPath

        blnBroke = .IsBroken

        If blnBroke = True Or Err <> 0 Then

          strPath = .FullPath

          Debug.Print " ***** Err = "; Err; " and Broke = "; blnBroke

          With Access.References

            .Remove loRef

            Debug.Print "path name = "; strPath

            .AddFromFile strPath

          End With

        End If

       End With

    Next

  

    If intCount < 9 Then Call AddRefs ' This one has 9 references

      

  Set loRef = Nothing



  ' Call a hidden SysCmd to automatically compile/save all modules.

  Call SysCmd(504, 16483)

End Function



Function AddRefs()

    Dim loRef As Access.Reference

    Dim intCount As Integer

    Dim intX As Integer

    Dim blnBroke As Boolean

    Dim strPath As String



    On Error Resume Next



    'Loop through each reference in the database

    'Add all references

    Debug.Print "----------------- Add References -----------------------"

        

          If Application.Version = "16.0" Then ' Microsoft Office 2016

            With Access.References

                .AddFromFile "C:\Program Files\Common Files\System\ado\msadox.dll" 'ADO Ext 6.0

                .AddFromFile "C:\Program Files\Common Files\System\ado\msado15.dll" 'Active X Data Objects 6.1

                .AddFromFile "C:\Program Files\Microsoft Office\OFFICE16\Excel.exe"  'For Excel Calls

                .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\OFFICE16\MSO.dll" 'Microsoft Office Obj Library

                .AddFromFile "C:\Program Files\Microsoft Office\OFFICE16\MSOUTL.OLB"  'For Outlook Calls

            End With

          End If

        

          If Application.Version = "15.0" Then ' Microsoft Office 2013

            With Access.References

                .AddFromFile "C:\Program Files\Common Files\System\ado\msadox.dll" 'ADO Ext 6.0

                .AddFromFile "C:\Program Files\Common Files\System\ado\msado15.dll" 'Active X Data Objects 6.1

                .AddFromFile "C:\Program Files\Microsoft Office\OFFICE15\Excel.exe"  'For Excel Calls

                .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\OFFICE15\MSO.dll"

                .AddFromFile "C:\Program Files\Microsoft Office\OFFICE15\MSOUTL.OLB"  'For Outlook Calls

            End With

          End If

        

  ' Call a hidden SysCmd to automatically compile/save all modules.

  Call SysCmd(504, 16483)

End Function



Function WaitFor(NumOfSeconds As Long)

Dim SngSec As Long

SngSec = Timer + NumOfSeconds



Do While Timer < SngSec

DoEvents

Loop

End Function

Just my two cents.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:41
Joined
Oct 29, 2018
Messages
21,542
john_S said:
You can always auto add the reference

Code: 
Option Compare Database

Option Explicit

Function FixUpRefs()

    Dim loRef As Access.Reference

    Dim intCount As Integer

    Dim intX As Integer

    Dim blnBroke As Boolean

    Dim strPath As String

    Dim strVersion As String

  

    On Error Resume Next



    'Count the number of references in the database

    intCount = Access.References.Count

  

    'Loop through each reference in the database

    'and determine if the reference is broken.

    'If it is broken, remove the Reference and add it back.

    Debug.Print "----------------- References found -----------------------"

    Debug.Print " reference count = "; intCount



    For intX = intCount To 1 Step -1

      Set loRef = Access.References(intX)

      With loRef

        Debug.Print " reference = "; .FullPath

        blnBroke = .IsBroken

        If blnBroke = True Or Err <> 0 Then

          strPath = .FullPath

          Debug.Print " ***** Err = "; Err; " and Broke = "; blnBroke

          With Access.References

            .Remove loRef

            Debug.Print "path name = "; strPath

            .AddFromFile strPath

          End With

        End If

       End With

    Next

  

    If intCount < 9 Then Call AddRefs ' This one has 9 references

      

  Set loRef = Nothing



  ' Call a hidden SysCmd to automatically compile/save all modules.

  Call SysCmd(504, 16483)

End Function



Function AddRefs()

    Dim loRef As Access.Reference

    Dim intCount As Integer

    Dim intX As Integer

    Dim blnBroke As Boolean

    Dim strPath As String



    On Error Resume Next



    'Loop through each reference in the database

    'Add all references

    Debug.Print "----------------- Add References -----------------------"

        

          If Application.Version = "16.0" Then ' Microsoft Office 2016

            With Access.References

                .AddFromFile "C:\Program Files\Common Files\System\ado\msadox.dll" 'ADO Ext 6.0

                .AddFromFile "C:\Program Files\Common Files\System\ado\msado15.dll" 'Active X Data Objects 6.1

                .AddFromFile "C:\Program Files\Microsoft Office\OFFICE16\Excel.exe"  'For Excel Calls

                .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\OFFICE16\MSO.dll" 'Microsoft Office Obj Library

                .AddFromFile "C:\Program Files\Microsoft Office\OFFICE16\MSOUTL.OLB"  'For Outlook Calls

            End With

          End If

        

          If Application.Version = "15.0" Then ' Microsoft Office 2013

            With Access.References

                .AddFromFile "C:\Program Files\Common Files\System\ado\msadox.dll" 'ADO Ext 6.0

                .AddFromFile "C:\Program Files\Common Files\System\ado\msado15.dll" 'Active X Data Objects 6.1

                .AddFromFile "C:\Program Files\Microsoft Office\OFFICE15\Excel.exe"  'For Excel Calls

                .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\OFFICE15\MSO.dll"

                .AddFromFile "C:\Program Files\Microsoft Office\OFFICE15\MSOUTL.OLB"  'For Outlook Calls

            End With

          End If

        

  ' Call a hidden SysCmd to automatically compile/save all modules.

  Call SysCmd(504, 16483)

End Function



Function WaitFor(NumOfSeconds As Long)

Dim SngSec As Long

SngSec = Timer + NumOfSeconds



Do While Timer < SngSec

DoEvents

Loop

End Function

Just my two cents.
Click to expand...
Hi John. This thread is a little old, but thanks for sharing. Cheers!
 
isladogs

isladogs

MVP / VIP
Local time
Today, 01:41
Joined
Jan 14, 2017
Messages
18,261
...but using late binding is much easier
 
You must log in or register to reply here.

Similar threads

A
Solved Moved to New Computer, New Version of Access Issue with acFormatPDF
Replies
18
Views
417
pbaldy
pbaldy
M
VBA - Reference issue MSO.DLL -> VBEUI.DLL
Replies
3
Views
422
Gasman
Gasman
Guus2005
Solved Broken references
Replies
3
Views
514
The_Doc_Man
The_Doc_Man
P
Access On New Computer
2
Replies
35
Views
904
amorosik
amorosik
E
Problem with linking
Replies
4
Views
400
ellenr
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom