reading an Excel sheet for input

[johnsun1976]

Hi. I have this code to open an Excel worksheet and read it in.. When I try to read in the first column, which is a list of numbers with may have a letter (ie. 4, 4a, 4b, 5,..), the letters are stripped (ie. 4a comes in a 4). Does anyone know what I'm doing wrong?
Thanks
John


Dim cnXL As New ADODB.Connection
Dim rs As New ADODB.Recordset

'open Excel through ADO
Set cnXL = New ADODB.Connection
With cnXL
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = _
"Data Source=" & filepath & ";" & _
"Extended Properties=""Excel 8.0;" & _
"HDR=no);"""
.Open
End With

rs.Open "Select * from [Sheet1$]", cnXL, adOpenStatic
Dim LotNumber As String

With rs
.MoveFirst

While Not .EOF
LotNumber = .Fields("LotNumber&quot
Wend
End with

 

[AndyWatt]

What do you do with LotNumber when you've read it in? Your code as it stands just reads .Fields("LotNumber&quot once.

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[johnsun1976]

Whoops.. I must have cut that out..

LotNumber is a string.. I declared it "Dim LotNumber as String"

Thanks
John

 

[AndyWatt]

Sorry, I possibly didn't explain that too well.

What I meant was:

You are reading a value from a cell and putting that value into LotNumber. I can see no reason why it would truncate to one character - how "4A" becomes "4" - unless some other operation is being performed on LotNumber. What I was trying to ascertain is what happens to LotNumber after the code snippet you posted.

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux

 

[Swi]

It seems as if it can not detect the datatype of the field and therefore generating undersirable results. When I try your code I get a NULL string returned. The only way I got VB to return consistent results was to following this example on Microsoft's site:

Q194124

Just type the above into their search engine. This illustration uses DAO. You may want to look into the MAXSCANROWS option in the extended properties if you want to use ADO.

Swi