XL: "Out of Memory" Message Using the OpenText Method

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows

SYMPTOMS

If you open a text file while recording a Visual Basic macro in Microsoft Excel 5.0, when you run the resulting subroutine, you may receive an "Out of memory" message if the text file you opened contains over more than 69 columns.

In Microsoft Excel 7.x, you will receive the error message if the text file contains more than 55 columns. In Microsoft Excel 97, you will receive the error message if the text file contains more than 52 columns.

CAUSE

This problem occurs because of the way this process is recorded.

When you record the process of opening a text file, an array of arrays is created to store the parse information in the FieldInfo argument of the OpenText method. This uses a considerable amount of memory.

WORKAROUND

This problem can be avoided by modifying the recorded subroutine so that it uses three separate arrays.

To work around this problem, create a two-dimensional array to store the parsed information. The first dimension of the OpenText methods FieldInfo parameter specifies the column number. The second dimension represents the data type of each column. For a list of the data type parameters for the OpenText method, search on "OpenText" in the Excel Visual Basic help file.

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

Method 1: If you are using text files that have a delimited columns, use

          the following macro:

          Sub OpenText_Ex1()
             Dim ColumnArray(1 To 100, 1 To 2) As Integer
             Dim x As Integer

             ' Create a For-Next that populates the two dimensions of
             ' the ColumnArray array.
             For x = 1 To 100
                ColumnArray(x, 1) = x
                ColumnArray(x, 2) = 1
             Next x

             ' Open the delimited text file using ColumnArray as the
             ' FieldInfo parameter.
             Workbooks.OpenText _
                Filename:="C:\TEST.TXT", DataType:=xlDelimited, _
                FieldInfo:=ColumnArray
          End Sub

          If you want to open a text file and explicitly define specific
          columns as a certain data type, create one array that contains
          the columns you want to explicitly define and one that contains
          the data type of those columns. Columns that are not explicitly
          defined will be opened with the General data type, which is the
          default.

          The following example opens a text file called "C:\Test.txt" that
          has at least 100 delimited columns, with columns 1, 2, 3, 4, 99,
          and 100 explicitly defined as data types of either 9 or 3. A data
          type of 9 will skip the corresponding column. A data type of 3
          will define the column as a Date in the format of "MDY."

          Sub OpenTextFile_Ex2()
             Dim ColumnsDesired
             Dim DataTypeArray
             Dim ColumnArray(0 To 5, 1 To 2)

             ' Define the specific column information in two arrays.
             ColumnsDesired = Array(1, 2, 3, 4, 99, 100)
             DataTypeArray = Array(9, 3, 3, 9, 3, 2)

             ' Create a For-Next that populates the two dimensions of
             ' the ColumnArray array.
             For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
                ColumnArray(x, 1) = ColumnsDesired(x)
                ColumnArray(x, 2) = DataTypeArray(x)
             Next x

             ' Open the delimited text file using ColumnArray as the
             ' FieldInfo parameter.
             Workbooks.OpenText _
                Filename:="C:\TEST.TXT", DataType:=xlDelimited, _
                FieldInfo:=ColumnArray
          End Sub

          With fixed-width text files, the OpenText method uses the first
          dimension of the fieldInfo array as the starting character for
          each column; therefore, you must explicitly define the starting
          location of each column in the first dimension of the array. The
          starting position of the first character in a fixed width text
          file is 0.

          The following example opens a fixed-width text file, and creates
          a new column every 4 characters. Characters after the 23rd are
          not opened.

          Sub OpenTextFile_Ex3()
             Dim ColumnSizes
             Dim DataTypeArray
             Dim ColumnArray(0 To 5, 1 To 2)

             ' Define the specific column information in two arrays.
             ColumnSizes= Array(3, 8, 12, 16, 20, 24)
             DataTypeArray = Array(1, 1, 1, 1, 1, 9)

             ' Create a For-Next that populates the two dimensions of
             ' the ColumnArray array.
             For x = LBound(ColumnSizes) To UBound(ColumnSizes)
                ColumnArray(x, 1) = ColumnSizes(x)
                ColumnArray(x, 2) = DataTypeArray(x)
             Next x

             ' Open the fixed width text file using ColumnArray as the
             ' FieldInfo parameter.
             Workbooks.OpenText _
                Filename:="C:\TEST.TXT", DataType:=xlFixedWidth, _
                FieldInfo:=ColumnArray
          End Sub