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.
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:
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Private Sub Form_Load()
.ConnectionString = "DSN=Excel Files;" & _
"DBQ=" & App.Path & "\test.xls;" & _
"DefaultDir=" & App.Path & ";DriverId=22;" & _
"MaxBufferSize=2048;PageTimeout=5;" & _
"Initial Catalog=" & App.Path & "\test;"
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)
Set cmd = Nothing
Set cn = Nothing
- 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.
- Close Form1.
- 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.
Article ID: 293828 - Last Review: Aug 4, 2008 - Revision: 1