Reading an Excel database from Word using VBA

[Roderick]

I'm now using a laptop with Windows 7, 64-bit and Office 2013, 32-bit.

I'm testing out a macro enabled document in Word attempting read an Excel file I use as a database. When it works correctly, I'll incorporate the proper coding into the template where it should be.

I must say, that it worked correctly in my Windows 10 32-bit system with Office 2016, 32-bit. But in this setup... Not a chance.

I've tugged and I've pushed and read countless conflicting ideas and suggestions but without any luck.

Below is part of the function which gives me the problem:

 
Public Function xlFillList(ComboBox As Object, iColumn As Long, strWorkbook As String, _
    strRange As String, RangeIsWorksheet As Boolean, RangeIncludesHeaderRow As Boolean)
    Dim RS As Object
    Dim CN As Object
    Dim numrecs As Long, q As Long
    Dim strWidth As String
    strRange = strRange & "]"
    Set CN = CreateObject("ADODB.Connection")
    ' CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" &
    CN.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strWorkbook & "My Office Details.xls" & _
    "Extended Properties" = "Excel 8.0;HDR=YES"";"
    '"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Set RS = CreateObject("ADODB.Recordset")
    RS.CursorLocation = 3
    RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 'read the data from the worksheet
    With RS
        .MoveLast
        numrecs = .RecordCount
        .MoveFirst
    End With
    With ComboBox
        .ColumnCount = RS.Fields.Count
        If RS.RecordCount > 0 Then
            .Column = RS.GetRows(numrecs)
        End If

The problem always arises when the function reaches this statement:

'CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" &
CN.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strWorkbook & "My Office Details.xls" & _
"Extended Properties" = "Excel 8.0;HDR=YES"";"
'"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

You will see that I have commented out a couple of lines both above and below the statement in question to see if different things worked. But no - no joy!

Depending what action I take it seems to give me a couple of errors:

Data source name not found and no default driver found

Or

Could not find installable driver

None of those statements give me any help when I research them.

the strWorkbook gives me the correct string for the file path.

I have set a reference to the Microsoft ActiveX Data Object 2.8 Library.

I'm at my wits' end to try and resolve this challenge but without any success.

Can somebody with fresh eyes please show me where I'm going wrong?

Thanks!