Problem on reading Excel file by ADO

[mkmkmk2]

Hi guys,

I have a function in VB to connect information from a excel file (xls) by ado.

I'm having problems on a column mix with number and english characters. I found that when the field only contain number, the system will fail to read the value and return NULL.
Note: No special cell formatting have been apply to the excel file, all cells are using default General type.

Example:
The A2 cell will return NULL, wile A1 and A3 are fine

A B C D
policy119 CompanyA FileA.txt Comment1
2678 CompanyB FileB.txt Comment2
188policy CompanyC FileC.txt Comment3


rstData.Source = "Select * From [" & tableName & "]"
rstData.Open, objExcelConn

Do While Not rstData.EOF
If IsNull(.Fields("A").Value) = False Then
PrintReport (.Fields("A").Value,.Fields("B").Value,.Fields("C").Value,.Fields("D").Value)
else
CounterErr = CounterErr + 1
End If
rstData.MoveNext
Loop


Can somebody help

Thanks a lot.



NOTE:
1) I found that if I manually add an apostrophe (" ' " char) before the number will solve the problem, any idea why it act like this?
2) And I have a few thousand of records like this, any suggestion on let the program identify the field only contain number and need to add the " ' " before getting the value in VB.

A B C D
policy119 CompanyA FileA.txt Comment1
'2678 CompanyB FileB.txt Comment2
188policy CompanyC FileC.txt Comment3

[Hack]

Welcome to DevX

What is the function you have?

Typically I use Excel Automation with VB6 rather than a database connectivity object like ADO.

[mkmkmk2]

Welcome to DevX

What is the function you have?

Typically I use Excel Automation with VB6 rather than a database connectivity object like ADO.

Hi,

As the client PC did not thave the Excel installed, so that I try to use ADO to connect data. What is Excel Automation? Does it require Excel obj to embedd in my VB program?
Thanks.

[Hack]

the client PC did not thave the Excel installed

Then using Automation won't work for you. Sorry to have intruded.

[Ron Weller]

ADO scans the first row of excel data and based on the values
in each column it decides what data type to make that field in
the resulting recordset.
As it loads each row of data into it's recordset it ignores items
that won't fit in the fields data type.

[mkmkmk2]

Hi Ron,
Thanks