VB excel Null values !! Please help me !!
 

Hi everyone,

I have these lines in my excel file,

Item Code Qty
9119299 3
9119399 3
AR350001 1

I am reading this file using VB 6.0 and I am using
"Microsoft.Jet.OLEDB.4.0" as the provider to read this excel file. For the
first two lines I am getting null vales in VB.. ? and other line is okey ..
any idea why is that happen and how to fix this problem. Please help me ..
!!!!

-Aruna

VB excel Null values !! Please help me !!
 

I copied your data into a workbook and I can read it all. So, I suspect, the clue to your problem lies in the connection string and/or SQL your are using to read the data. What are your connection string and SQL?

VB excel Null values !! Please help me !!
 

I do not understand & I cannot see your attachment. I tried using the ODBC driver instead

Cnn="Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book2.xls;
Sql="Select * from [Sheet1$]
Set ADORS=CreateObject("ADODB.RecordSet"
ADORS.Open Sql,Cn

I get the following data in the record set

9119299
9119399

I understand this result: the driver determines that column 1 is numeric and the third row is non numeric and it therefore does not read it (replaces it with null).

I also get the same result with the JET 4.0 driver

If, in your xls file, columns 1, rows 2 & 3 are right justified, they are numeric, row 3 should be left justifies as it is a literal. All rows in Column 2 should be right justified

I haven't got any further suggestions.

VB excel Null values !! Please help me !!
 

I had another thought

I think your sheet has non numeric content in column 1, rows 4/5 onwards. So, when the driver/provider scans the default number of rows in every column to determine the data type, it concludes that Column 1 is character. As a consequence, the value in row 2 & 3 being numeric are discarded and replaced by nulls

VB excel Null values !! Please help me !!
 

Hi,

I replaced the driver and now it seems to be okay.. Thanks a lot
for your valuable time ..

Thanks again
-Aruna


"AA2e72E" wrote in message
...
I do not understand & I cannot see your attachment. I tried using the ODBC
driver instead.

Cnn="Provider=MSDASQL;Driver={Microsoft Excel Driver
(*.xls)};DBQ=c:\book2.xls;"
Sql="Select * from [Sheet1$]"
Set ADORS=CreateObject("ADODB.RecordSet")
ADORS.Open Sql,Cnn

I get the following data in the record set:

9119299 3
9119399 3
1
I understand this result: the driver determines that column 1 is numeric
and the third row is non numeric and it therefore does not read it (replaces
it with null).

I also get the same result with the JET 4.0 driver.

If, in your xls file, columns 1, rows 2 & 3 are right justified, they are
numeric, row 3 should be left justifies as it is a literal. All rows in
Column 2 should be right justified.

I haven't got any further suggestions.