Excel returns blank values (Not IMEX bug) using ADO

[ManishV]

I have a big spreadsheet and I have written some VB code
that reads the spreadsheet using ADO. The range that I am
reading, has numeric values. The cells in the first row
have Numeric Format and the rest of the cells in the
second row onwards, have a Custom Format of '[=0]"";0'
which means 'if the value is 0, then just return blank
string ("&quot else return the value with a precision of one
decimal place'.
The ADO/VB returns the correct values for the first row
but for some of the rest of the rows, esp. for the second
row, it returns Blank string (Not Null, though) even if
there is some value in the cell.
One more thing, if I change the Format of the cells with
Custom format to Numeric/General format, the ADO returns
correct results.
I am aware of a bug in the ADO that Excel returns Null
values when there is data type mismatch, but I had faced
that issue in the same spreadsheet but in a different
range. And I have already fixed that issue using the
workaround suggested by Microsoft, using "IMEX=1". That
range is fixed. I do not think it is the same issue as ADO
does not return Null values but a blank string.

Also, if I use ODBC-Excel driver instead of ADO, then it
does return me correct results but the problem with that
is there is known bug, without a workaround to date, with
ODBC that it always assumes that the first row has column
headers and hence ignores the first row. Microsoft
suggests to use ADO instead of ODBC if first row does not
contain headers. Also, I'll need to make a lot of changes
and test it, to switch to ODBC instead of ADO as I have
been using ADO for long time.

Now, I am wondering if there is anything that I could do
without making any changes to the spreadsheet so that ADO
returns me the correct values.

I would really appreciate your help as I have almost exhaused my options.

Cheers
Manish

 

[CCLINT]

Have you tried using the JET provider instead?

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\Results\Orders1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

(I haven't digested your post in detail, nor do I know if this solution will help. But it's worth a try)

 

[ManishV]

CCLINT,

My apologies, when I said ADO, I mean 'Jet.OLEDB'. I am using exactly the same connection string that you have posted.

I would post the message again with bit more information. Please feel free to point out the areas the information is not clear enough.

I have a big spreadsheet and I have written some VB code
that reads the spreadsheet using OLEDB Provider for Jet. The range that I am reading, has numeric values. The cells in the first row have the headers and the second row onwards I have numeric values. The seconds cells in the second row have the Format (Format Cells) of Numeric. The third row onwards, have a Custom Format (Format Cells) of '[=0]"";0' which means 'if the value is 0, then just return blank string ("&quot else return the value with a precision of one decimal place'.
The OLEDB Jet Provider returns the correct values for the second row but for some of the rest of the rows, it returns Blank string (Not Null, though) even if there is some value in the cell.
One more thing, if I change the Format of the cells from
Custom Format ('[=0]"";0') to Numeric format, the Jet Provider returns correct results.
I am aware of a bug in the Jet Provider that Excel returns Null values when there is data type mismatch, but I had faced that issue in the same spreadsheet but in a different
range. And I have already fixed that issue using the
workaround suggested by Microsoft, using "IMEX=1". That
range is fixed. I do not think it is the same issue as Jet Provider does not return Null values but a blank string.

Also, if I use ODBC-Excel driver instead of Jet Provider, then it does return me correct results but the problem with that is there is known bug, without a workaround to date, with ODBC that it always assumes that the first row has column headers and hence ignores the first row. Microsoft
suggests to use Jet Provider instead of ODBC if first row does contain headers. Also, I'll need to make a lot of changes and test it, to switch to ODBC instead of Jet Provier as I have been using Jet Provider for long time.

Now, I am wondering if there is anything that I could do
without making any changes to the spreadsheet so that Jet Provider returns me the correct values.

I would really appreciate your help as I have almost exhaused my options.

Cheers
Manish