Importing data may take longer than expected or Excel may appear to stop responding (hang) when you import data into a workbook in Excel 2002 or in Office Excel 2003 (911580)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002

SYMPTOMS

When you import data into a workbook in Microsoft Excel 2002 or in Microsoft Office Excel 2003, you may experience the following symptoms:
  • The time that is required to import the data may take longer than expected.
  • Microsoft Excel may appear to stop responding (hang) for several minutes before completing the importation of the data.

CAUSE

This behavior may occur when the following conditions are true regarding the data that is being imported into the workbook:
  • It consists mostly of string data instead of numeric data.
  • The string data is unique and does not match the existing string pool that is already in the workbook.
When you import data into a workbook, Excel tries to match the incoming string data to an existing string pool to save space and to increase performance for searches, filtering, and other string operations. If the incoming string data is unique, no matches for it will be found and it will be added to the existing string pool as new string data. As the existing string pool expands, the time that is required by Excel to find a match for new string data increases. When this occurs, you experience the symptoms that are described in the "Symptoms" section.

WORKAROUND

To work around this behavior, use one of the following methods.

Method 1: Reduce the amount of data to import

Instead of importing all the data into the workbook at the same time, specify a range of data to import.

Use this method two or more times until you have imported all the data into the workbook.

Method 2: Create an existing string pool

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Before you import the data into the workbook, create an existing string pool with lots of unique strings in the workbook. To do this, follow these steps:
  1. Start Excel, and open the workbook where you want to import the data.
  2. Add a temporary worksheet to the workbook.
  3. Click to select the worksheet that you added in step 2, and then press ALT+F11 to start Microsoft Visual Basic Editor.
  4. On the Insert menu, click Module.
  5. In the code window, create the following macro:
    Public Sub GrowStringPool()
    Dim row As Integer
    Dim col As Integer
    Dim text As String
    text = "Here is some text:"
    For col = 1 To 21
        For row = 1 To 1000
            Cells(row, col).Value = text + Str(row * col)
            row = row + 1
        Next
        col = col + 1
    Next
End Sub
  6. Run the macro on the new worksheet. The macro will fill the existing string pool in the worksheet with unique strings.
  7. Save the workbook.
  8. Close and reopen the workbook. Excel will evaluate that the workbook already contains a large existing string pool. This will prevent the addition of new string data into the existing string pool.
  9. Delete the temporary worksheet that you added in step 2.
After you complete these steps, the data should take less time to import into the workbook.
Modification Type:MajorLast Reviewed:12/7/2005
Keywords:kbPerformance KbVBA kbProgramming kbAutomation kbImport kbExpertiseInter kbnofix kbprb KB911580 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.