Add references programmatically

So yeah, this answer is a bit late, but just in case someone stumbles across this like I did looking for an answer, I figured out the following bit of code to add an excel reference and it seems to work fine, also in MDE/ACCDE!

If Dir("C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe") <> "" And Not refExists("excel") Then    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe")End IfIf Dir("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe") <> "" And Not refExists("excel") Then    Access.References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe")End IfIf Dir("C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.exe") = "" And Dir("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe") = "" Then    MsgBox ("ERROR: Excel not found")End If

And the refExists references the following function:

Private Function refExists(naam As String)Dim ref As ReferencerefExists = FalseFor Each ref In References    If ref.Name = naam Then        refExists = True    End IfNextEnd Function

If you ship an MDE/ACCDE you can't update your references.

But what specific references are causing you your problems? Chances are you are referencing Word, Excel or Outlook. If so use late binding so your solution doesn't matter what version is installed on the client system.

Late binding means you can safely remove the reference and only have an error when the app executes lines of code in question. Rather than erroring out while starting up the app and not allowing the users in the app at all. Or when hitting a mid, left or trim function call.

This also is very useful when you don't know what version of the external application will reside on the target system. Or if your organization is in the middle of moving from one version to another.

For more information including additional text and some detailed links see the "Late Binding in Microsoft Access" page.

Here is an example - it check for certain references - deleting them and importing the Access 2000 variant. Just to make sure all clients use the same (lowest) version of the dependencies

Sub CheckReference()' This refers to your VBA project.    Dim chkRef As Reference ' A reference.    Dim foundWord, foundExcel As Boolean    foundWord = False    foundExcel = False    ' Check through the selected references in the References dialog box.    For Each chkRef In References        ' If the reference is broken, send the name to the Immediate Window.        If chkRef.IsBroken Then           Debug.Print chkRef.Name        End If        If InStr(UCase(chkRef.FullPath), UCase("MSWORD9.olb")) <> 0 Then            foundWord = True        End If        If InStr(UCase(chkRef.FullPath), UCase("EXCEL9.OLB")) <> 0 Then            foundExcel = True        End If        If InStr(UCase(chkRef.FullPath), UCase("MSWORD.olb")) <> 0 Then            References.Remove chkRef        ElseIf InStr(UCase(chkRef.FullPath), UCase("EXCEL.EXE")) <> 0 Then            References.Remove chkRef        End If    Next    If (foundWord = False) Then        References.AddFromFile ("\\pathto\database\MSWORD9.OLB")    End If    If (foundExcel = False) Then        References.AddFromFile ("\\pathto\database\EXCEL9.OLB")    End IfEnd Sub