The information in this article applies to:
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 more than 69
columns.
CAUSE
This problem occurs because of the way this process is recorded.
WORKAROUNDMicrosoft provides programming examples 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 article assumes that you
are familiar with the programming language being demonstrated and the tools used to
create and debug procedures. Microsoft support professionals 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 needs. If you have limited
programming experience, you may want to contact a Microsoft Certified Solution Provider
or the Microsoft fee-based consulting line at (800) 936-5200. For more information about
Microsoft Certified Solution Providers, please see the following page on the World Wide Web:
http://www.microsoft.com/mcsp/For more information about the support options available from Microsoft, please see the following page on the World Wide Web: http://www.microsoft.com/support/supportnet/overview/overview.aspThis 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. Method 1: Use If You Are Using Text Files That Have Delimited ColumnsIf you are using text files that have delimited columns, use the following macro:
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."
Method 2: Use If You Are Using a Text File with Fixed-Width ColumnsWith 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.
Additional query words: OpenText Parse XL97 XL7 XL5
Keywords : kbprg |
Last Reviewed: August 12, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |