Problem on reading Excel file by ADO
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
