vb and excel as datasource

[rob merritt]

here is my code below everything goes well until

Set rst = oConn.Execute(strSQL)

it says it cant find the object 29-Dec-2003 (this is the worksheet
name for sure)
I have tried this with the sheet named ws1 and without the a2:5000
any ideas whats going on?

Private Sub Form_Load()
Dim rst As ADODB.Recordset
Dim oConn As ADODB.Connection
Set oConn = CreateObject("ADODB.Connection")
MsgBox App.Path & "\caretaker.xls"
strSQL = "SELECT * FROM [29-Dec-2003$a2:G500]"
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="
& App.Path & "\caretaker.xls;DefaultDir=" & App.Path
Set rst = oConn.Execute(strSQL)

 

[Harald Staff]

Hi

According to mr Erlandsen's page
http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado
you must use either the first worksheet or a named range.

HTH. Best wishes Harald

 

[Guest]

a. I did not think that you had to use the first sheet: you can use any sheet
b. You cannot UPDATE an excel ODBC data source; I tend to create a record set object and open it without reference to a connection object
c. I thought, incorrectly, there might be some issue with the name of the sheet

I tried

Cnn="Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=" & App.Path & "\caretaker.xls
Sql= "SELECT * FROM [29-Dec-2003$a2:G500]
Set ADORS=CreateObject("ADODB.Recordset"
ADORS.Open Sql,Cn

It works for me

 

[Jamie Collins]

...
...

According to mr Erlandsen's page
http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado
you must use either the first worksheet or a named range.

I think Mr Erlandsen's comments are instructions on how to call his
function. It is certainly possible to query worksheets other than the
first without using a defined Name (named range).

The hyphen (-) characters in the OP's sheet name means it must be
enclosed in single quotes, including the dollar ($) sheet name
indicator character, in order to be recognized e.g.

SELECT * FROM ['29-Dec-2003$']

Jamie.

--

 

[Harald Staff]

Thanks for the correction both of you. I love being wrong about limitations.

Best wishes Harald