Search code examples
excelvbarecordset

Why vba ado recordset does not get values from Excel table?

Good Day.

I have a problem with writing values from excel spreadsheet to ado recordset.

The spreadsheet "prox" contains the column of values such as:

  • 958371030M
  • 1200118
  • 982140615D
  • 1502680
  • KB7W-62864

This column has text type.

I use following code:

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & wd & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No;"";"

objRecordset.Open "Select * FROM [prox$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Then recordset contains

  • 958371030M
  • 982140615D
  • KB7W-62864

but numbers in text format are missed. On their places are empty spaces and 

Debug.Print IsEmpty(objRecordset.Fields.Item(3).Value)

returns 'True' for them.

Solution

  • You might need to add the IMEX option

    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & wd & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 8.0;HDR=No;IMEX=1;"";"

    For further details look at this SO's post