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!