OpenText Method

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.

Syntax

expression**.OpenText(Filename**, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator)

expression   Required. An expression that returns a Workbooks object.

Filename   Required String. Specifies the file name of the text file to be opened and parsed.

Origin   Optional Variant. Specifies the origin of the text file. Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the method uses the current setting of the FileOrigin option in the TextImportWizard.

StartRow   Optional Variant. The row number at which to start parsing text. The default value is 1.

DataType   Optional Variant. Specifies the column format of the data in the file. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. The default value is xlDelimited.

TextQualifier   Optional Variant. Specifies the text qualifier. Can be one of the following XlTextQualifier constants: xlTextQualifierDoubleQuote, xlTextQualifierSingleQuote, or xlTextQualifierNone. The default value is xlTextQualifierDoubleQuote.

ConsecutiveDelimiter   Optional Variant. True to have consecutive delimiters considered one delimiter. The default is False.

Tab   Optional Variant. True to have the tab character be the delimiter (DataType must be xlDelimited). The default value is False.

Semicolon   Optional Variant. True to have the semicolon character be the delimiter (DataType must be xlDelimited). The default value is False.

Comma   Optional Variant. True to have the comma character be the delimiter (DataType must be xlDelimited). The default value is False.

Space   Optional Variant. True to have the space character be the delimiter (DataType must be xlDelimited). The default value is False.

Other   Optional Variant. True to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value is False.

OtherChar   Optional Variant (required if Other is True). Specifies the delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored.

FieldInfo   Optional Variant. An array containing parse information for individual columns of data. The interpretation depends on the value of DataType.

When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the xlColumnDataType constants listed in the following table, specifying how the column is parsed.

You can use xlEMDFormat only if you have installed and selected Taiwanese language support. The xlEMDFormat constant specifies that Taiwanese era dates are being used.

The column specifiers can be in any order. If there's no column specifier for a particular column in the input data, the column is parsed with the General setting. This example causes the third column to be skipped, the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.

Array(Array(3, 9), Array(1, 2))

If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two-element array specifies the parse option for the column as a number between 1 and 9, as listed in the preceding table.

The following example parses two columns from a fixed-width text file. The first column includes characters 1 through 10. Characters 11, 12, 13, 14, and 15 are skipped. The second column includes character 16 through the last character in the line.

Array(Array(0, 1), Array(10, 9), Array(15, 1))

DecimalSeparator   Optional String. The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting.

ThousandsSeparator   Optional String. The thousands separator that Excel uses when recognizing numbers. The default setting is the system setting.

The following table shows the results of importing text into Excel for various import settings. Numeric results are displayed in the rightmost column.