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

Article translations Article translations
Article ID: 293828 - View products that this article applies to.
This article was previously published under Q293828
Expand all | Collapse all

On This Page

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.

MORE INFORMATION

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.

Properties

Article ID: 293828 - Last Review: May 8, 2003 - Revision: 2.1
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
Keywords: 
kbbug kbdatabase kbjet kbnofix KB293828

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com