[Manish]

Hi guys,

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 ("") 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.

Guys, I would really appreciate your help.

Cheers
Manish