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

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.

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

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

Steps to Reproduce Behavior

Create the Excel Spreadsheet

1. 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.
2. Open Test.xls. Type the following information in the cells:
   1. Select cell A1, and type CustomerID.
   2. Select cell B1, and type CustomerName.
   3. Select cell A2, and type 1.
   4. Select cell B2, and type Test.
3. Use the Autofill feature to fill column A with a series of numbers as follows:
   1. Select cell A2.
   2. 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.
   3. 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.
4. Use the Autofill feature to copy the Test value in cell B2 to row 1500 as follows:
   1. Select cell B2.
   2. Right-click the fill handle, drag the fill handle to row 1500, and then click Copy Cells.
5. Save the Excel spreadsheet, and close Excel.
6. 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.

Create the Visual Basic Project

1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
2. From the Project menu, click References, set a reference to Microsoft ActiveX Data Objects 2.X, and then click OK.
3. 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 = "DSN=Excel Files;" & _
               "DBQ=" & App.Path & "\test.xls;" & _
               "DefaultDir=" & App.Path & ";DriverId=22;" & _
               "MaxBufferSize=2048;PageTimeout=5;" & _
               "Initial Catalog=" & App.Path & "\test;"
               .Open
       End With
       
       Set cmd = New ADODB.Command
       cmd.ActiveConnection = cn
       
       cmd.CommandText = "update [Sheet1$] set [CustomerName]=? where [CustomerID] = ?"
       cmd.Parameters.Append cmd.CreateParameter("CustomerName", adChar, adParamInput, 10, "j")
       cmd.Parameters.Append cmd.CreateParameter("CustomerID", adInteger, adParamInput, 4, 4)
   
       cmd.Execute
       cn.Close
       Set cmd = Nothing
       Set cn = Nothing    
   End Sub
   					

4. Save the Visual Basic project and Form1 in the same folder as the Excel spreadsheet that you created in the previous steps.
5. Run the project, and ensure that Form1 appears.
6. Close Form1.
7. 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).
8. 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.