Importing Excel with mixed format in the one column - gives NULL values (VB6 - ADODB)

I'm using VB8, ADODB to import Excel data..
One Column has a mixture of cell formats.. (Numeric and Text)
Eg.  Col A1
  10012    (Numeric)
  10024A  (Text)
The ADODB will only report values from only ONE of these types.. Ie. In one run, it will treat all the numerics as NULL..  In another run, all the Text will be NULL.
From my understanding of database, a fields can't behave like a Variant..
So, is there a solution other than pre-checking the excel files and forcing Text format on these columns? Most of my clients using this code don't have excel and so can't fix the worksheets.

In case its relevant, my connection style is as follows..
  Dim XLcn As ADODB.Connection
  Dim rsX As ADODB.Recordset

  XLcn = "Provider=MSDASQL.1; Driver={Microsoft Excel Driver (*.xls)}; DriverId=790;MaxBufferSize =2048; PageTimeout=5;"
  rsX.Open "Select * From [Sheet1$]", XLcn

  rsX.Fields(0).Value '<- This value extracts as NULL when the data is numeric 10023
Who is Participating?
JohnBPriceConnect With a Mentor Commented:
While r0bertdenir0 is right, there is another option.  Open the spreadsheets using Excel Automation instead of the Excel-ADODB driver, then you can access each cells actual value.  Add a project reference to "Microsoft Excel 11.0 Object Library" (or as appropriate according to your version of Excel), and then do something like this....

    Dim xl As New Excel.Application
    Dim wbSource As Excel.Workbook
    Dim wsSource As Excel.Worksheet
    Dim r As Integer
    Set wbTarget = xl.Workbooks.Open(txtTargetFile.Text, , False)
    If wbTarget.ReadOnly Then
        MsgBox "Error, target opened read only"
        Exit Sub
    End If
    Set wsTarget = wbTarget.Worksheets(1)
    For r = 1 To wsTarget.UsedRange.Rows.Count
	'Do your stuff here using wsTarget.Cells(r, DesiredColumn).Value
    MsgBox "Done"

Open in new window

r0bertdenir0Connect With a Mentor Commented:
This is the behaviour of the Excel ODBC driver.
Unlike a database, you can't tell Excel the type of a column - a column can have multiple datatypes.
To determine the type of a column so that it behaves like a normal database, the Excel ODBC driver scans the first 8 rows of data & etermines which datatype is in the majority.
That majority datatype becomes the data type for that column as far as the driver is concerned.
All data not of that type in that column is discarded.
The only thing you can do is prepare the Excel spreadsheets so they only contain text & have yr code coerce them to the data type you desire.
Unfortunately you can't tell the Excel ODBC driver to treat all columns as text & import everything so you can have yr way with it.
It will always do it's 8 row scan & figure out the data type.
Also, altho the driver has an option to change the 8 rows to another number, this option has always been ignored by the driver & as far as I know the driver hasn't been updated to fix this.
All Courses

From novice to tech pro — start learning today.