Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file bloat using ADO
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL FILE BLOAT CAUSING FILE CRASH | Excel Discussion (Misc queries) | |||
File Bloat | Excel Discussion (Misc queries) | |||
File Bloat | Excel Discussion (Misc queries) | |||
How do you remove file bloat in Excel? | Excel Discussion (Misc queries) | |||
excel file size bloat | Excel Discussion (Misc queries) |