chergh
blah
- Local time
- Today, 12:45
- Joined
- Jun 15, 2004
- Messages
- 1,414
I'm doing some stuff in excel and some of it is going to be easier to get results of calculations by using sql rather than coding excel functions.
For some of these calculations I am wanting to use the following sort of sql.
Now the tables in both these instances are excel named ranges which are in two different workbooks. As I can't add multiple workbooks to a single dao database object I am trying to add a link to the second table in the database object. I've got the following code:
The code however fails on the last line giving me a run time error 3251 "Operation is not supported for thistype of object". anyone know if this is possible and if not got any suggestions on how I can acheive this.
I know could just just create a second database object and then copy the data into the same workbook and then create a named range and then use that in my SQL statement would rather use an alternative if I can.
For some of these calculations I am wanting to use the following sort of sql.
Code:
select stuff from table
where stuff not in (select things from otherTable)Now the tables in both these instances are excel named ranges which are in two different workbooks. As I can't add multiple workbooks to a single dao database object I am trying to add a link to the second table in the database object. I've got the following code:
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim linkpath As String
Dim path As String
path = Workbooks("otd3.xls").path & "\otd3.xls"
linkpath = Workbooks("lead times.xls").path & "\lead times.xls"
Set db = DAO.OpenDatabase(path, False, False, "Excel 8.0")
Set tdf = db.CreateTableDef("LeadList")
tdf.Connect = "Excel 8.0;DATABASE=" & linkpath
tdf.SourceTableName = "Blah"
db.TableDefs.Append tdfThe code however fails on the last line giving me a run time error 3251 "Operation is not supported for thistype of object". anyone know if this is possible and if not got any suggestions on how I can acheive this.
I know could just just create a second database object and then copy the data into the same workbook and then create a named range and then use that in my SQL statement would rather use an alternative if I can.
