Coding for diff versions of "Microsoft ActiveX Data Objects" Library

[shell_l_d]

I'm still having issues with an Excel spreadsheet with macro's...
to make it multi-compatible for Office & Windows versions.
In particular for ADODB in 'Microsoft Active X Data Object #.# Library' (C:\Program Files\Common Files\System\ado\msado*.dll) & mscomct2.ocx.

I developed it in 2010 & am using it in my 2007 without problems (using 6.0 Library), however a work colleague uses 2007 & has a different version (2.8 Library) installed & it keeps failing.

I've been comparing GUID's but appears there are many different GUID's for each different 'Microsoft Active X Data Object #.# Library' ...

Extracts only:

        For Each chkRef In vbProj.References
 
            n = n + 1
            sName = chkRef.Name
            sFile = chkRef.FullPath
            sFullPath = chkRef.FullPath
            sDescr = chkRef.Description
            sGuid = chkRef.GUID
 
            ' set sName, sFile & sDescr based on current library/reference
            ' this is beneficial for non installed libraries as name, descr & fullpath will be blank
            If chkRef.GUID = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}" Then
                sName = "MSComCtl2"
                sFile = "mscomct2.ocx"
                sDescr = "Microsoft Windows Common Controls-2 #.#"
            ElseIf chkRef.GUID = "{B691E011-1797-432E-907A-4D8C69339129}" Then
                sName = "ADODB"
                sFile = "mmsado15.dll"
                sDescr = "Microsoft ActiveX Data Objects 6.0 Library (part of MDAC)"
            ElseIf chkRef.GUID = "{2A75196C-D9EB-4129-B803-931327F72D5C}" Then
                sName = "ADODB"
                sFile = "mmsado15.dll"
                sDescr = "Microsoft ActiveX Data Objects 2.8 Library (part of MDAC)"
            End If
 
            ' Check for MISSING REFERENCES 
            If chkRef.isbroken = True And (sFullPath <> "") Then
                ' REFERENCE NOT REGISTERED
                ' Potential to fix reference without user intervention per:
                ' http://www.vbaexpress.com/kb/getarticle.php?kb_id=267
                ' http://www.mrexcel.com/forum/showthread.php?t=442346
 
                ' Set to continue in case of error
                On Error Resume Next
 
                ' Remove the MISSING reference
                'vbProj.References.Remove chkRef    '  *** LEFT THIS OUT ***
 
                'Clear any errors so that error trapping for GUID additions can be evaluated
                Err.Clear
 
                ' Add the reference - use zero for major & minor versions to pick latest.
                vbProj.References.AddFromGuid GUID:=sGuid, Major:=0, Minor:=0
 
                'If an error was encountered, inform the user
                Select Case Err.Number
                Case Is = 32813
                    'Reference already in use.  No action necessary
                Case Is = vbNullString
                    'Reference added without issue
                Case Else
                    'An unknown error was encountered, so alert the user
                    MsgBox "A problem was encountered trying to add VbReference:" _
                            & vbNewLine & "GUID: " & sGuid _
                            & vbNewLine & "Name: " & sName _
                            & vbNewLine & "Description: " & sDescr _
                            & vbNewLine _
                            & vbNewLine & "Please check the references in your VBA project (VbReferences)!" _
                            , vbCritical + vbOKOnly, sProcSig & " Error!"
                    Call RefNotInstalled(sName, sFile, sDescr, sGuid)
                End Select
 
            ElseIf (sFullPath <> "") Then
                'IS REGISTERED, do nothing
            Else
                ' REFERENCE NOT INSTALLED
                    Call RefNotRegistered(sName, sFullPath, sDescr, sGuid)
            End If
 
        Next chkRef

The AddFromGuid wont work in this instance unless I code for all the possible GUID's for ADODB.

Same goes for AddFromFile as filename can be different too
(eg: msado15.dll or msado20.dll):
C:\Program Files\Common Files\System\ado\msado*.dll

 

[shell_l_d]

Still having problems with libraries/objects between different versions of Excel (& Windows)...

My spreadsheet has: 7 x worksheets

On Sheet1 ("Update") it has these:
2 x DTPickers shows: =EMBED("MSComCtl2.DTPicker.2","")
1 x combo box shows: =EMBED("Forms.ComboBox.1","")
2 x buttons shows nothing for formula

It also uses ADODB (to get data from database)

I can run the spreadsheet on my pc with Excel2007 (Win7), others can & others cant.
Also fails on Excel2010 (WinVista), same errors as an Excel 2007 (WinVista) user.
So seems to be working for Excel 2007 + Win7, but so far failing for (some/all?) Excel 2007/2010 + Vista.

Fails for Worksheets("Update") .Select & .Range("....") & on combobox code.
I can see the worksheet/ranges/combobox all exist & are named properly.

Error '32809 Application-defined or object-defined error' occurred in ThisWorkbook.Workbook_Open at line 15.
... same for lines 16 to 23.

===========================================

Sub Workbook_Open()

          ' For Error Reporting
          Dim sErrorDescription As String
          Const sProcSig As String = MODULE_NAME & "Workbook_Open"
          'On Error Resume Next
1         On Error GoTo Error_In_WorkbookOpen

2         With ActiveWorkbook
          
              'Name cells for 'LAST UPDATE STATISTICS'
3             .Names.Add Name:="StartDate", RefersTo:="=Update!$E$5"
4             .Names.Add Name:="EndDate", RefersTo:="=Update!$E$8"
5             .Names.Add Name:="FilterField", RefersTo:="=Update!$E$11"
6             .Names.Add Name:="FilterFieldIndex", RefersTo:="=Update!$E$12"
7             .Names.Add Name:="LastUpdated", RefersTo:="=Update!$E$14"
8             .Names.Add Name:="UpdateStatus", RefersTo:="=Update!$E$15"
              
              'Name cells for 'VB Libraries Info'
9             .Names.Add Name:="NumFoundLibs", RefersTo:="=Update!$E$20"
10            .Names.Add Name:="NumMissingLibs", RefersTo:="=Update!$E$21"
11            .Names.Add Name:="NumBrokenLibs", RefersTo:="=Update!$E$22"
              
              ' Clear values for 'VB Libraries Info' on 'Update' worksheet
12            Call ClearVbReferences

13        End With

14        With Worksheets("Update")
          
              ' Date/Time Format
15            .Range("StartDate").NumberFormat = "dd mmm yyyy"
16            .Range("EndDate").NumberFormat = "dd mmm yyyy"
17            .Range("LastUpdated").NumberFormat = "dd mmm yyyy"
          
              ' Fill combo box
18            .cbFilterField.Clear
19            .cbFilterField.AddItem ("CallTime")
20            .cbFilterField.AddItem ("TechComp")
21            .cbFilterField.AddItem ("CloseDate")
22            .cbFilterField.AddItem ("Calc Compl Date")
              ' Set default value
23            .cbFilterField.ListIndex = 0
          
24        End With
          
25        Call FixDTPickers

      ' ===== Exit Handler =====
Exit_WorkbookOpen:
26        Application.ScreenUpdating = True
27        Exit Sub

      ' ===== ERROR HANDLER =====
Error_In_WorkbookOpen:

28        With Err
29            sErrorDescription = "Error '" & .Number & " " & _
                  .Description & "' occurred in " & sProcSig & _
                  IIf(Erl <> 0, " at line " & CStr(Erl) & ".", ".")
30        End With

31        Select Case MsgBox(sErrorDescription, vbAbortRetryIgnore, "Error in " & sProcSig)
              Case vbRetry
32                Resume
33            Case vbIgnore
34                Resume Next
35            Case Else
36                Resume Exit_WorkbookOpen
37            End
38        End Select
          
End Sub

===========================================

Errors at line 12 > goes to Error Handler > fails at line 112 with:
Run-time error '32809'
Application-defined or object-defined error

===========================================

Public Sub DataExtract()
...[more code]...              
          ' Set 'last updated' statistics (dates)
4         Application.StatusBar = False
5         Application.ScreenUpdating = False
6         With ActiveWorkbook.Worksheets("Update")
              
              ' Obtain the start & end dates from the DTPickers
              ' this may cause errors... consider late binding of DTPickers
7             On Error Resume Next
8             dteStartDate = Format(.DTPickerStart, "yyyy-mm-dd")
9             dteEndDate = Format(.DTPickerEnd, "yyyy-mm-dd")
10            filterField = .cbFilterField.ListIndex
11            On Error GoTo Error_In_DataExtract
          
12            .Select
13            .Range("StartDate").Value = dteStartDate
14            .Range("EndDate").Value = dteEndDate
15            .Range("FilterField").Value = .cbFilterField.Value
16            .Range("FilterFieldIndex").Value = filterField
17            .Range("LastUpdated").Value = Date
18            .Range("UpdateStatus").Value = "Started"
              
19        End With
...[more code]...              

' ===== ERROR HANDLER =====
Error_In_DataExtract:
          
          'On Error Resume Next
110       Application.StatusBar = "Error occurred..."
111       With ActiveWorkbook.Worksheets("Update")
112           .Select
113           .Range("UpdateStatus").Value = "Failed"
114       End With
...[more code]...

===========================================


Been testing with:
Excel 2007 with Vista / Win7
Excel 2010 with Vista

This code seems to work fine for ADODB library, but the other 4 libraries included are unable to be removed/added as they say they are 'in use', yet I'm getting errors as shown above.

Sub AddReference(sName As String, sGuid As String)
'
' Attempts to remove then add the VB Reference
' Potential to fix reference without user intervention per:
'   http://www.vbaexpress.com/kb/getarticle.php?kb_id=267
'   http://www.mrexcel.com/forum/showthread.php?t=442346
' Call AddReference( "ADODB", "{00000205-0000-0010-8000-00AA006D2EA4}" )
'
    
    ' For Error Reporting
    Dim sErrorDescription As String
    Const sProcSig As String = MODULE_NAME & "AddReference"
    On Error GoTo Error_In_AddReference
    
    With ActiveWorkbook.VBProject.References
        .Remove .Item(sName)
        .AddFromGuid sGuid, 0, 0
    End With
    
    ' ===== Exit Handler =====
    Exit Sub

' ===== ERROR HANDLER =====
Error_In_AddReference:
    
    With Err
        sErrorDescription = "Error '" & .Number & " " & _
            .Description & "' occurred in " & sProcSig & _
            IIf(Erl <> 0, " at line " & CStr(Erl) & ".", ".")
    End With

    Select Case Err.Number
       Case Is = -2147352565
            ' Ignore, as already removed. Error: Incorrect function
            'MsgBox "VB Reference " & sName & " already removed"
       Case Is = 32813
            'Ignore, already in use. Error: Name conflicts with existing module, project, or object library
            'MsgBox "VB Reference " & sName & " already installed"
       Case Else
            'An unknown error was encountered, so alert the user
            MsgBox "Unable to add VbReference " & sName _
                & vbCrLf _
                & vbCrLf & sErrorDescription _
                , vbInformation, "Error in " & sProcSig
    End Select
    Resume Next
        
End Sub

P.S. I have 5 images on another forum... but not sure if I'm allowed to post the link here, couldn't see anything in the rules about links.

Need solution/fix please.

 

[shell_l_d]

I just deleted the 2 buttons & saved, closed, re-opened the spreadsheet.
Problem still exists.

However... when I try to delete the combo box by selecting Design Mode > click combo box > Delete, it will crash excel. This is in Excel2010. Will have to haven't tried removing the combo box using Excel 2007... anyhow just trying to pinpoint what object on the "Update" worksheet could be causing the errors/problems.

It would be so much easier if all Excel versions were multi-compatible... doh...