SQL on Column with Text and Numbers

[Ed_P via OfficeKB.com]

I am trying to SQL data from a closed excel workbook(Data.xls) into my
current, the code below works fine, however, the data that I am querying has
a column that can contain data as text or numbers (eg 7+ or 7). The result
returned from my SQL string only returns the text values, any numbers are
left blank. Is there some way to return everything regardless of the data
type?


Dim ConString As String
Dim strSQL As String
Dim DBPATH As String
Dim recordset As New ADODB.recordset

DBPATH = ThisWorkbook.Path & "\Data.xls"

ConString = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & DBPATH & ";" & _
"extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [Data$]"

Set recordset = New ADODB.recordset

On Error GoTo cleanup:

Call recordset.Open(strSQL, ConString, adOpenForwardOnly, adLockReadOnly,
CommandTypeEnum.adCmdText)

Call Sheets("DataDump").Range("A2").CopyFromRecordset(recordset)

Set recordset = Nothing

 

[Jean-Yves]

Hi Ed,
I am not an expert in DB or SQL, but :
A database field should be defined as a certain data type eg text or
integer.
I think that a "General"cell format is not the solution in excell.
regards
JY