1

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.

braX
  • 11,506
  • 5
  • 20
  • 33
zhildemon
  • 65
  • 3

1 Answers1

0

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

Storax
  • 11,158
  • 3
  • 16
  • 33