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!

[macropod]

On the Win 7 system, try deleting any references with version 16.0 in them and replacing them with the version 15.0 equivalents and re-setting the Microsoft ActiveX Data Object 2.8 Library reference. Check, too, that any other reference paths are correct.

[gmayor]

That looks like one of my code sequences and the original should work if you have the Microsoft ACE driver installed
https://www.microsoft.com/en-us/down....aspx?id=23734
and of course you supply the macro with the full path of the Excel file and the worksheet name.

[Roderick]

Thank you, Graham.

Yes, it is part of your code that you displayed in an earlier post but I used that in Windows 10, 32-bit and Office 2016, 32-bit.

When I finally want to distribute the template with the code that works in this system, it could be working in users' machines running anything: from Windows 7 to Windows 10 64-bit.

I might add that present users, at least most of them, might be using Windows 7 with original templates referring to 'DAO' and I haven't had any shouts from them - yet! These were written six years ago and still seem to be going strong. However, I've been advised in this forum not to use DAO now.

I'm trying to make this a "one size fits all" project.

But again, thanks for your advice and I'll work with it.

[Roderick]

Thanks, Paul.

How can I check that the reference paths are the correct ones, please? And what should they be?

As you can see from my reply to Graham, these templates, when distributed, have to work in all sorts of machines.

Nevertheless, I'm very grateful for the help and advice received in this forum. I hope I can do it justice.

[gmayor]

As the old saying goes - If it ain't broke, don't fix it.

The Ace database engine should be available in Windows 7 and Windows 10, depending on which Office versions were installed, but the missing software (if that is the problem) can be downloaded and installed for those users who don't have it. I have users with a variety of system using that code or variations of it. Where appropriate I provide the link to

https://www.microsoft.com/en-us/down....aspx?id=23734