Rather than use a recordset, instead use INSERT INTO and UPDATE sql
statements to insert and append rows respectively.

--

am wrote in message . ..
I have modified a routine based on the following WebPage that uses ADO
to add data to closed Excel files.

Able Consulting, Inc.
How do I update an Excel spreadsheet using ADO?
Using OLE DB Provider for Jet
http://www.able-consulting.com/ADO_Faq.htm#Q15

---------------------------------------------------------------------------------------------------------

With approximately 2500 files set up exactly the same, the time to
open the files, transfer the data, and save the files was just north
of an hour. Using ADO, the time was reduced to around 10 minutes.

Unfortunately, using ADO, the files are bloating. The Used Range is
where it should be. If I open, save and close the ADO'd files, the
file size reduces to the about the same size prior to using ADO.

Starting size : 160Kb
After one pass : 375 Kb
After two passes : 450 Kb

After searching Google, the only 'solution' I found was in the
following MSKB article. Which only tells me what I had found out
accidentally.

BUG: Excel File Size Grows When You Edit or Update ADO Recordset on
Excel Spreadsheet
http://support.microsoft.com/default...;en-us;Q293828

RESOLUTION
When you open and resave the Excel workbook in the Excel application,
after the ADO updates have been applied, restores the workbook to a
smaller size.

Apparently, this is a problem with Access files, given the number of
postings that I found searching Google. Access has a tool for
compacting databases, although most posters weren't thrilled with that
'solution'.

---------------------------------------------------------------------------------------------------------

Has anyone experienced this problem? If so, did you find a solution?

Alternately, has anyone found a solution for writing data to closed
Excel files (without the bloat) that significantly reduces the time in
opening, transferring data, saving, and closing the files?

TIA

Paul