SQL on Column with Text and Numbers

  • Thread starter Ed_P via OfficeKB.com
  • Start date

E

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
 
Ad

Advertisements

J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top