Applies To
Workbooks Collection.
Description
Loads and parses a text file as a new workbook with a single sheet containing the parsed text-file data.
Syntax
object.OpenText(filename, origin, startRow, dataType, textQualifier, consecutiveDelimiter, tab, semicolon, comma, space, other, otherChar, fieldInfo)
object
Required. The Workbooks object.
filename
Required. Specifies the filename of the text file to open and parse.
origin
Optional. Specifies the origin of the text file (one of xlMacintosh, xlWindows, or xlMSDOS). If this argument is omitted, the method uses the current setting in the File Origin option of the Text Import Wizard.
startRow
Optional. The row number at which to start parsing text. The first row is 1. If omitted, 1 is assumed.
dataType
Optional. Specifies the column format of the data within the file (either xlDelimited or xlFixedWidth). The default is xlDelimited.
textQualifier
Optional. Specifies the text qualifier. Can be one of xlDoubleQuote, xlSingleQuote, or xlNone. The default is xlDoubleQuote.
consecutiveDelimiter
Optional. True if consecutive delimiters should be considered as one delimiter. The default is False.
tab
Optional. True if dataType is xlDelimited and the tab character is a delimiter. The default is False.
semicolon
Optional. True if dataType is xlDelimited and the semicolon character is a delimiter. The default is False.
comma
Optional. True if dataType is xlDelimited and the comma character is a delimiter. The default is False.
space
Optional. True if dataType is xlDelimited and the space character is a delimiter. The default is False.
other
Optional. True if dataType is xlDelimited and the character specified by the otherChar argument is a delimiter. The default is False.
otherChar
Optional (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, remaining characters are ignored.
fieldInfo
Optional. An array containing parse information for the 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 (one based), and the second element is one of the following numbers specifying how the column in parsed:
1 General
2 Text
3 MDY date
4 DMY date
5 YMD date
6 MYD date
7 DYM date
8 YDM date
9 Skip the column
The column specifiers may be in any order. If a column specifier is not present for a particular column in the input data, the column is parsed using 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 of each two-element array specifies the starting character position in the column (as an integer; character zero is the first character). The second element of the two-element array specifies the parse option for the column as a number from one through nine, as listed above.
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 characters 16 through the last character in the line.
Array(Array(0, 1), Array(10, 9), Array(15, 1))
See Also
Close Method, Open Method, TextToColumns Method.
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