DAO and Multiple Excel Workbooks

[chergh]

I'm doing some stuff where it's a lot easier to get results using SQL but I'm not sure how to do the following.

I've got named ranges in two workbooks so if I want to query them I just use

Please Login or Register  to view this content.

Which works fine but for some calculations I want to write a SQL statement along the lines of:

Please Login or Register  to view this content.

Is this possible?

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 was just wondering if there was anyway of having multiple workbooks in the same database.

Cross posted

[Stuie]

Hi chergh,

Can you not make a string variable to get the value from the second workbook like so

Please Login or Register  to view this content.

and then change SQL string to this

Please Login or Register  to view this content.

see if that would work

[chergh]

No that doesn' seem to work. I would think the table (named range) would need to exist within the same database object. Hmmm I might be able to add it as a linked table, time to look at the the help files again.

[chergh]

Ok I'm now getting a run time error 3251 "Operation is not supported for this
type of object" with the following code, the error occurs on the last line

Please Login or Register  to view this content.

Anyone got any ideas?

[Kenneth Hobson]

ADO would be better. See GetData at Ron's site. http://www.rondebruin.nl/ado.htm

[chergh]

I'm not wanting to copy the data across, thats a last resort.