Another thought: if your data source isn't an open workbook, is it
then a closed Excel workbook, MS Access or SQL Server database e.g.
you're using the MS OLDDB provider for Jet?

If so you may be able to use the SELECT..INTO or INSERT INTO..SELECT
with the IN keyword (both are MS proprietary syntax) to update all
your workbooks without having to open them. Not only would it avoid
the problem of the workbook remaining open (because you never opened
it!) it also happens to be faster.

BTW this morning I had the problem you described i.e. vba project
remains in the VBE after the workbook was closed. Cause? I'd
inadvertently been querying an open workbook!

--

"Kent Prokopy" wrote in message ...
Yes I am using ADO. But not on an open excel file.

Stephen, I am going to try your suggestion below. Thank you.


"onedaywhen" wrote in message
om...
Just a thought: are you using ADO to query an open workbook, perhaps
using Jet's SELECT..INTO syntax targeting the active workbook, thus
falling foul of the ADO memory leak bug (MSDN Q319998)?

--

Stephen Bullen wrote in message
...
Hi Kent,

Workbooks.Open "FilePathAndName.xls"

Instead of relying on the ActiveWorkbook, I prefer to use a direct
reference:

Set oBook = Workbooks.Open(...)

'Do Stuff

oBook.Close SaveChanges:=True

Populate with some data from ADO calls...This populates up to 100
sheets
of data in each workbook.

This is where I'd imagine your problem lies, with a reference leaking
out. If
you comment out those lines, does the rest work fine?

Alternatively, are there any other addins that might be hooking a
workbook_open event?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk