Excel for ADO RecordSet has null values ​​for numeric cells

Question

I am trying to import some data to work from an Excel file, but I am having problems with the numerical values ​​of the cells. I have several columns that will have values, where some of them are numeric, while other values ​​can be a combination of numeric and non-numeric values ​​(without special characters, only the letters AZ). To output data to a recordset, I do the following

Set oconn = New ADODB.connection oconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & excelFile & ";" & "Extended Properties=""Excel 8.0;HDR=YES;""" sTableName = "[sheet1$]" sTableName = "select * from " & sTableName Set oRs = New ADODB.Recordset oRs.Open sTableName, oconn, adOpenStatic, adLockOptimistic 

When I read the values ​​of the recordset, the numerical values ​​are displayed as empty in the columns where the data is in a mixed format. Is there a way to make a recordset just read all values ​​as text or an alternative way to read an Excel file to avoid this problem?

Answer 1

I had a similar problem, but in the reverse order, where the numbers turned out to be perfect, but there were no alpha. After adding

;IMEX=1

for advanced properties, it worked fine.

IMEX=1 means always read mixed data columns as text.

Answer 2

If you can later exclude it, the easiest way to overcome this is to always set the first eight lines of your data to the type you want. Despite the ability to specify "lines to scan" in ODBC, it turns out that it always scans the first 8 lines. If you can afford to have some dummy data at the top (this is later excluded by rotation, etc.), this works well! You can see the Microsoft link here: https://support.microsoft.com/en-us/kb/141284