VBA Code to change References.... . . 1

[Chunkus]

Hi

I work in an office that has difference versions of MS office, AND we share databases.

I need code that will change references depending on which PC the database is used on.

Where Excel 97 is used I need the MS excel 8.0 reference ticked, and where excel 2003 is used I need the MS excel 11.0 reference ticked.

Anybody got any code to do this PLEASE?

The problem is detailed here:

http://support.microsoft.com/kb/q194374/

However, I cannot get the MS solutuon to work.

I know my problem is caused by differencet versions of excel. The excel reference comes up missing - I manually change it to my version of excel, but it will then not work on other people PCs. They subsequently change the excel reference to their version - but it will then not work on mine. Round and round we go!!!.. . .

Hence the need for code to autotmatcially change the excel reference dependent on current version being used.

Can Anyone help? Driving me mad!!!!!


CHEERS

Jason

 

[jadams0173]

Function SetReferences()
On Error GoTo err
    Dim strRef As String, strSQL As String, strFile As String, blnRef As Boolean
    Dim ref As Reference
    
    For i = 1 To 6
        Select Case i
        Case 1
            strRef = "VBA"      'Visual Basic for Applications
            strFile = "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL"
        Case 2
            strRef = "Access"   'Microsoft Access Object Library
            strFile = "C:\Program Files\Microsoft Office\Office\MSACC9.OLB"
        Case 3
            strRef = "stdole"   'OLE Automation
            strFile = "C:\WINNT\system32\stdole2.tlb"
        Case 4
            strRef = "DAO"      'Microsoft DAO 3.6 Object Library
            strFile = "C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.DLL"
        Case 5
            strRef = "Outlook"  'Microsoft Outlook Object Library
            strFile = "C:\PROGRA~1\MICROS~2\Office\msoutl9.olb"
        Case 6
            strRef = "Excel"    'Microsoft Excel Object Library
            strFile = "C:\Program Files\Microsoft Office\Office\EXCEL9.OLB"
        End Select
        
        For x = 1 To References.Count
            Set ref = References(x)
            If ref.Name = strRef Then
                blnRef = True
                Exit For
            End If
        Next x
        If blnRef = False Then
            Set ref = References.AddFromFile(strFile)
        End If
        blnRef = False
    Next i
    Exit Function
    
err:
    MsgBox err.Number & ": " & err.Description

End Function

This is how I make sure references are set when I know what is needed. Myabe this will get you started.

 

[Chunkus]

Thanks Mate.

With some minor changes this worked perfectly!

Saved me loads of time.

CHEERS, and av a star for your troubles!

Jason

 

[JerryKlmns]

I think a reference to ADO 2.1 shall always work for every1 unless you need the extras of 2.5 or above

VBE6.DLL + stdole2.tlb + DAO360.DLL are all common and must be on the same directory of ever1 (usually)

For the rest there always is ... LATE ... binding!

Dim objExcel As Object
set objExcel = CreateObject("Excel.Application")
Dim objOutlook As Object
set objOutlook = CreateObject("Outlook.Application")

But you lose intellisense! OYDEN KALO AMIGES KAKOY