Recordset based on Excel

[RobS23]

I'm using the excel driver to connect to a spreadsheet like so...

Path = "C:\EDI.xls"

Set EConnection = CreateObject("ADODB.Connection&quot
EConnection.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & Path & ";ReadOnly=0;UID=admin;"
Set ADOCmd.ActiveConnection = EConnection

...now it appears to be connecting just fine. But, I'm not quite sure how to populate a recordset. I'm trying

ADOCmd.CommandType = adCmdUnknown

strSQL = "select * From sheet"

ADOCmd.CommandText = strSQL

Set recset = ADOCmd.Execute

But I'm getting an error in the 'From' clause message. So how do I use a SQL string with the Excel driver??

My file is called 'EDI.xls' and the Worksheet is named 'sheet' any help???

 

[daimaou]

I'm not sure you can do a select * from a MS Excel spreadsheet. Try to add column headers and select by their names. Hopefully it'll work.

If not, I'm not sure if this would do anything, but I think it would be better to try to open your recordset like:

Dim rsExcel as new ADODB.Recordset

With EConnection
.Provider = "Microsoft.jet.OLEDB.4.0"
.ConnectionString = "Datasource= C:\EDI.xls;Extended Properties=Excel 8.0;"
.Open
End With

strSQL = "select field1, field2 From sheet" 'For example

rsExcel.Open strSQL, cnExcelConnection, adOpenDynamic, adLockOptimistic

This should do it. Let me know if this helped.

 

[RobS23]

As usual with one of those irritating SQL moments it was a (yet another) twist on SQL syntax - fields have to be enclosed in those ` things. i.e.

"select `field1`, `field2` From `sheet`"

Thanks for the refinement of the connection code though

 

[hughg]

Indeed!, Those (') things can be extremely annoying (and time consuming to eliminate, especially in situations where you also have to use a lot of these (&quot things too).

Generally to avoid blindness, etc., I use a simple string variable like so:

Dim strApostrophe as string
strApostrophe = chr(39)

'example
strSQL = "SELECT * FROM [X] WHERE [Y] = " & Apostrophe & SomeVar & Apostrophe & "
'as opposed too
strSQL = "SELECT * FROM [X] WHERE [Y] = "'" & SomeVar & "'"

It's longer code, but a lot easier to read.
Cheers,
Hughg