Microsoft KB Archive/293828

= BUG: Excel File Size Grows When You Edit or Update ADO Recordset on Excel Spreadsheet =

Article ID: 293828

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.5 Service Pack 1
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q293828



SYMPTOMS
When you update a Microsoft Excel spreadsheet through an ActiveX Data Objects (ADO) Recordset, the Excel file size grows. This behavior is most evident when you use the ADO parameters collection to make changes to the underlying spreadsheet.

It appears that the percentage that the file size grows is not determined by the number of updates that are completed, but by the size of the original Excel spreadsheet. This growth can sometimes be as much as double the original file size.



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



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Create the Excel Spreadsheet
 Create a new Microsoft Excel spreadsheet named Test.xls, and save Test.xls in a folder. You will also save the new Visual Basic project that you create in the second set of steps to this folder. Open Test.xls. Type the following information in the cells:  Select cell A1, and type CustomerID . Select cell B1, and type CustomerName . Select cell A2, and type 1 . Select cell B2, and type Test .</li></ol> </li> Use the Autofill feature to fill column A with a series of numbers as follows: <ol style="list-style-type: lower-alpha;"> Select cell A2.</li> Locate the small black square in the lower right corner of the selected cell, which is called the fill handle. Point to the fill handle until your pointer changes into a plus (+) sign.</li> Right-click the fill handle, drag the fill handle to row 1500, and then click Fill Series. This should increment the numbers in column A from 1 to 1499.</li></ol> </li> Use the Autofill feature to copy the Test value in cell B2 to row 1500 as follows: <ol style="list-style-type: lower-alpha;"> Select cell B2.</li> Right-click the fill handle, drag the fill handle to row 1500, and then click Copy Cells.</li></ol> </li> Save the Excel spreadsheet, and close Excel.</li> In Windows Explorer, point to Documents, right-click Test.xls, and then click Properties. Make note of the file size that is listed on the General tab. During this test, the file size was approximately 86.5 KB.</li></ol>

Create the Visual Basic Project
<ol> Create a new Standard EXE project in Visual Basic. Form1 is created by default.</li> From the Project menu, click References, set a reference to Microsoft ActiveX Data Objects 2.X, and then click OK.</li>  Paste the following code in the code window of Form1: Option Explicit Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command

Private Sub Form_Load With cn       .ConnectionString = &quot;DSN=Excel Files;&quot; & _ &quot;DBQ=&quot; & App.Path & &quot;\test.xls;&quot; & _ &quot;DefaultDir=&quot; & App.Path & &quot;;DriverId=22;&quot; & _ &quot;MaxBufferSize=2048;PageTimeout=5;&quot; & _ &quot;Initial Catalog=&quot; & App.Path & &quot;\test;&quot; .Open End With Set cmd = New ADODB.Command cmd.ActiveConnection = cn   cmd.CommandText = &quot;update [Sheet1$] set [CustomerName]=? where [CustomerID] = ?&quot; cmd.Parameters.Append cmd.CreateParameter(&quot;CustomerName&quot;, adChar, adParamInput, 10, &quot;j&quot;) cmd.Parameters.Append cmd.CreateParameter(&quot;CustomerID&quot;, adInteger, adParamInput, 4, 4)

cmd.Execute cn.Close Set cmd = Nothing Set cn = Nothing End Sub </li> Save the Visual Basic project and Form1 in the same folder as the Excel spreadsheet that you created in the previous steps.</li> Run the project, and ensure that Form1 appears.</li> Close Form1.</li> <li>Browse to the folder that contains Test.xls. Right-click Test.xls, and then click Properties. Notice that the file size of Test.xls is much larger than the original file size. In this test, it was 165 KB (which is more than 90 percent larger).</li> <li>Reopen the modified workbook in Microsoft Excel and resave it. Right-click Test.xls in Windows Explorer, and then click Properties. Notice that the file size of Test.xls has now been restored approximately to its original file size.</li></ol>

Additional query words: inflate gets larger

Keywords: kbbug kbnofix kbdatabase kbjet KB293828

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.