SQL Query in Excel not returning correct data

[creepr91]

hi,

I trying to query an Excel file and only return certain fields and with specific criteria and populate a flexgrid.

My Excel sheet I am pulling the data from is Sheet1
and the first row of the columns I want are Model_Number and Date_Received

1. When I use SELECT * FROM [sheet1] I get all of the data
Any time I try to select specific fields I get no data.
I have tried SELECT [sheet1].model_number
I have tried SELECT [model_number]
I have tried SELECT model_number
I have tried SELECT field2
None return any data.

2. I am also having issues with my WHERE statements, but I guess since it is not recognizing my field names my problems are linked.

Any ideas? I do alot of SQL server and access programming, 1st time playing with Excel.

Here is my code for that section

Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "SELECT model_number FROM [sheet1$] WHERE [Model_Number] = "sps5669-2"

Set Rs1 = Cmd1.Execute()


Thanks,
Vince

[si_the_geek]

Welcome to VBForums!

This thread and this thread both use similar methods, and are working. The only obvious difference I can see is that one uses the format "SELECT [sheet1].[model_number]".

There may also be a difference in the connection string - but as you haven't posted yours I can't tell. If there is, it will presumably be with the "header" part, which is what lets ADO know where the field names are (otherwise the first row is assumed to be data).

I think I have also seen (but cannot find it now) the use of F1 for the field name of column 1 (then F2, etc) where field names were not available.

The Where clause you have posted is not quite right, it should be with single quotes like this: ...WHERE [Model_Number] = 'sps5669-2'"

[creepr91]

Here are my connection strings.
When export it into a txt deliminated file and use a txt connection string it works without a hitch, I will play around a little tonite.

ExcelConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\keipe_v\My Documents\sr.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""

TxtConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\keipe_v\My Documents\;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""