This article was previously published under Q293828
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
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.
Use the Autofill feature to fill column A with a series of numbers as follows:
Select cell A2.
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.
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.
Use the Autofill feature to copy the Test value in cell B2 to row 1500 as follows:
Select cell B2.
Right-click the fill handle, drag the fill handle to row 1500, and then click Copy Cells.
Save the Excel spreadsheet, and close Excel.
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
Create a new Standard EXE project in Visual Basic. Form1 is created by default.
From the Project menu, click References, set a reference to Microsoft ActiveX Data Objects 2.X, and then click OK.
Paste the following code in the code window of Form1:
Option ExplicitDim cn As New ADODB.ConnectionDim cmd As New ADODB.CommandPrivate 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
Save the Visual Basic project and Form1 in the same folder as the Excel spreadsheet that you created in the previous steps.
Run the project, and ensure that Form1 appears.
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).
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.
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