Option Compare Database
Const CON_EXCEL_REF_GUID = "{00020813-0000-0000-C000-000000000046}"
'the above constant is the excel guid

'----------------------------------------------------------
'adding stuff to deal with references, broken references, etc:
' Looping variable.
'from access Help:
Public Function confirmExcelRegistered()


    Dim ref As Reference
    Dim blnExcelOK As Boolean
    ' Enumerate through References collection.
    Dim refExcel As Reference, blnExcelBroken As Boolean, strExcelVBAGuid As String
    strExcelVBAGuid = CON_EXCEL_REF_GUID
    For Each ref In References
        ' Check IsBroken property.
        If ref.IsBroken = False Then
          If ref.Guid = strExcelVBAGuid Then
            'excel is ok
            blnExcelOK = True
          End If
          '  Debug.Print "Name: ", ref.Name
          '  Debug.Print "FullPath: ", ref.FullPath
          '  Debug.Print "Version: ", ref.Major & "." & ref.Minor
           '  Debug.Print "GUID: " & ref.Guid
            ' Debug.Print "builtin? " & ref.BuiltIn
           '  Debug.Print "---------------------------"
        Else
           ' Debug.Print "GUIDs of broken references:"
           ' Debug.Print ref.Guid
           ' Debug.Print "builtin? " & ref.BuiltIn
            If ref.Guid = strExcelVBAGuid Then
              Debug.Print "PROBLEM, excel is referenced, but is broken"
              blnExcelBroken = True
              MsgBox "PROBLEM!  Excel has been referenced already from this database, but is no longer accessible (the reference is broken).  Did you uninstall Excel or update to a new version?  Unfortunately, this means you cannot export from this database into Excel.  You need to download a new database and load the contents of this database into it.", vbCritical, CON_APP_TITLE
              Set refExcel = ref
            End If
            
        End If
    Next ref
    

    If Not blnExcelOK Then
      If Not blnExcelBroken Then
        'try to add it
        blnExcelOK = addExcelRef()
      End If
    End If
    
   confirmExcelRegistered = blnExcelOK
End Function

Private Function addExcelRef() As Boolean
  'first try to get the 2003 version, then go down to 2002 and 2000 if that doesn't work, then go up to future versions
   
   Dim intMinor As Integer, intMajor As Integer
   intMajor = 1 'default
   For intMinor = 5 To 3 Step -1
    
      If tryAddingExcelRef(intMajor, intMinor) Then GoTo exitthisOK
   Next intMinor
   
   'keep trying minor version up to 100, then up major versions
   For intMinor = 6 To 100
     If tryAddingExcelRef(intMajor, intMinor) Then GoTo exitthisOK
   Next intMinor
   
   'try a few major versions
   For intMajor = 2 To 10
     For intMinor = 1 To 100
       If tryAddingExcelRef(intMajor, intMinor) Then GoTo exitthisOK
     Next intMinor
   Next intMajor
    'adds 2003
    'Application.References.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 1, 4 'adds 2002
    'Application.References.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 1, 3 'adds 2000
    'didn't work:
    addExcelRef = False
    GoTo exitthis
    
    ''try adding anything else
exitthisOK:
   addExcelRef = True
exitthis:
  
Exit Function
End Function

Private Function tryAddingExcelRef(intMajor, intMinor) As Boolean
  'reports whether or not it could be added
   On Error GoTo cantAdd
    Application.References.AddFromGuid CON_EXCEL_REF_GUID, intMajor, intMinor
    
    Debug.Print "added Excel version: " & intMajor & "." & intMinor; " as reference to this project"
   'if we got here, it worked
   tryAddingExcelRef = True
   
exitthis:
   Exit Function
   
cantAdd:
   tryAddingExcelRef = False
   Resume exitthis
   
End Function