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

expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

expression Required. An expression that returns one of the objects in the Applies To list.

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. Additionally, this could be an integer representing the code page number of the desired code page. For example, "1256" would specify that the encoding of the source text file is Arabic (Windows). If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.

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. If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file.

 XlTextQualifier

XlTextQualifier can be one of these XlTextQualifier constants.

xlTextQualifierDoubleQuote default

xlTextQualifierNone

xlTextQualifierSingleQuote

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.

 XlColumnDataType

XlColumnDataType can be one of these XlColumnDataType constants.

xlGeneralFormat General

xlTextFormat Text xlMDYFormat MDY date xlDMYFormat DMY date xlYMDFormat YMD date xlMYDFormat MYD date xlDYMFormat DYM date xlYDMFormat YDM date xlEMDFormat EMD date xlSkipColumn Skip Column

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.

Notes

• If you specify that a column is to be skipped, you must explicitly state the type for all remaining columns or the data will not parse correctly.
• If there is a recognizible date in the data, the cell will be formatted as a date in the worksheet even if the setting for the column is General. Additionally, if you specify one of the above date formats for a column and the data does not contain a recognized date, then the cell format in the worksheet will be General.

This example causes the third column to be parsed as MDY (for example, 01/10/1970), 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, 3), 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 0 and 9, as listed in the preceding table.

TextVisualLayout  Optional Variant. The visual layout of the text.

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

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

 table

System decimal separator	System thousands separator	Decimal separator value	Thousands separator value	Text imported	Cell value (data type)
Period	Comma	Comma	Period	123.123,45	123,123.45 (numeric)
Period	Comma	Comma	Comma	123.123,45	123.123,45 (text)
Comma	Period	Period	Comma	123,123.45	123,123.45 (numeric)
Period	Comma	Period	Comma	123 123.45	123 123.45 (text)
Period	Comma	Period	Space	123 123.45	123,123.45 (numeric)

TrailingMinusNumbers  Optional Variant. Specify True if numbers with a minus character at the end should be treated as negative numbers. If False or omitted, numbers with a minus character at the end are treated as text.

Local  Optional Variant. Specify True if regional settings of the machine should be used for separators, numbers and data formatting.

Example

This example opens the file Data.txt and uses tab delimiters to parse the text file into a worksheet.

Workbooks.OpenText filename:="DATA.TXT", _
    dataType:=xlDelimited, tab:=True