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

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

onedaywhen,

Thanks for the info. Can you provide an example and/or references to
these two SQL statements? I've been searching Google for a while, but
I can't get a handle on it.

Thanks

Paul

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

On 1 Mar 2004 02:08:35 -0800, (onedaywhen)
wrote:

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

I didn't realize that 'INSERT INTO' was a Jet SQL statement. I could
not find any reference to it in either of two texts on SQL. I
accidentally found it while searching through more posts on Google.

After getting this to work, the file still bloats.

Paul

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

On Mon, 01 Mar 2004 10:08:58 -0700, am wrote:

onedaywhen,

Thanks for the info. Can you provide an example and/or references to
these two SQL statements? I've been searching Google for a while, but
I can't get a handle on it.

Thanks

Paul

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

On 1 Mar 2004 02:08:35 -0800, (onedaywhen)
wrote:

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

Actually, 'INSERT INTO' is ANSI standard SQL. Some proprietary
interpretations of SQL support just 'INSERT' e.g. SQL Server (but NOT
Jet).

--

am wrote in message . ..
I didn't realize that 'INSERT INTO' was a Jet SQL statement. I could
not find any reference to it in either of two texts on SQL. I
accidentally found it while searching through more posts on Google.

After getting this to work, the file still bloats.

Paul

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

On Mon, 01 Mar 2004 10:08:58 -0700, am wrote:

onedaywhen,

Thanks for the info. Can you provide an example and/or references to
these two SQL statements? I've been searching Google for a while, but
I can't get a handle on it.

Thanks

Paul

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

On 1 Mar 2004 02:08:35 -0800, (onedaywhen)
wrote:

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