"Out of Memory" Message Using the OpenText Method

This article was previously published under Q134826
This article has been archived. It is offered "as is" and will no longer be updated.
Symptoms
If you open a text file while recording a Visual Basic macro in MicrosoftExcel 5.0, when you run the resulting subroutine, you may receive an "Outof memory" message if the text file you opened contains more than 69columns.

In Microsoft Excel 7.x, you will receive the error message if the textfile contains more than 55 columns. In Microsoft Excel 97, you willreceive 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 iscreated to store the parse information in the FieldInfo argument of theOpenText method. This uses a considerable amount of memory.
Workaround
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. This problem can be avoided by modifying the recorded subroutine so that ituses three separate arrays.

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

Method 1: Use If You Are Using Text Files That Have Delimited Columns

If you are using text files that have 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				

Method 2: Use If You Are Using a Text File with Fixed-Width Columns

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				
OpenText Parse XL97 XL7 XL5 XL
Properties

Article ID: 134826 - Last Review: 10/26/2013 12:03:00 - Revision: 3.0

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • kbnosurvey kbarchive kbprogramming KB134826
Feedback