vb and excel as datasource
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) |
vb and excel as datasource
Hi
According to mr Erlandsen's page http://www.erlandsendata.no/english/...dacimportwbado you must use either the first worksheet or a named range. HTH. Best wishes Harald "rob merritt" skrev i melding om... 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) |
vb and excel as datasource
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 |
vb and excel as datasource
"rob merritt" wrote ...
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? "Harald Staff" wrote ... According to mr Erlandsen's page http://www.erlandsendata.no/english/...dacimportwbado 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. -- |
vb and excel as datasource
Thanks for the correction both of you. I love being wrong about limitations.
Best wishes Harald "Jamie Collins" skrev i melding om... It is certainly possible to query worksheets other than the first without using a defined Name (named range). |
All times are GMT +1. The time now is 11:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com