Blue Aardvark wrote:
Thanks for the info. I was led to believe that ADO was microsofts new
wondertoy

Indeed, if you mean circa 1999 <g. ADO 'classic' remains a great set
of components from the COM era. I use fabricated ADO recordsets to hold
in-memory data much more than I use Collections or Dictionary objects.

but it seems to have some definite issues

The memory leak bug is the only sigificant *ADO* issue, IMO. There are
other 'Excel Jet' issues, notably data typing, but these may be
designed around very effectively.

I would still like to
use sql to retrieve data from an open excel file as it would save me a lot of
hard coding vba.
I may be stuck repeatedly using the saveCopyAs function until my hard disk
burns out.

Personally, I don't think saving a copy is too much of a problem.
Remember you only need to save the source worksheet(s) to a new
workbook and close it.

Do you know any other tricks?

The alternatives may or may not cause memory leaks and other problems.
IMO querying an open workbook is not a good solution under any
circumstances. And, as I hinted earlier, I'm coming back to the idea
that if data is work querying with SQL then it's worth keeping in a SQL
database (Access/Jet, SQL Server/MSDE, non-MS products are also
available <g).

Jamie.

--