Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table from External Datasource of Excel Workbook | New Users to Excel | |||
How do I change the datasource location for a query in Excel 2003 | Excel Discussion (Misc queries) | |||
Security for Viewing MS Access Databases From Excel Datasource | Excel Discussion (Misc queries) | |||
Web form validation using Excel datasource? | Excel Programming | |||
Excel column as datasource | Excel Programming |