Open Text Method

This method will load a text file as a new workbook. The method will parse the text data and place it in a single worksheet. The rather complex syntax is:

WorkbooksObject.OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo)

Note first that all of the parameters to this method are optional except the FileName parameter.

The Filename parameter specifies the filename of the text file to be opened.

The Origin parameter specifies the origin of the text file and can be one of the following

XlPlatform constants:

Enum XlPlatform xlMacintosh = 1 xlWindows = 2 xlMSDOS = 3 End Enum

Note that the xlWindows value specifies an ANSI text file, whereas the xlMSDOS constant specifies an ASCII file. If this argument is omitted, the current setting of the File Origin option in the Text Import Wizard will be used.

The StartRow parameter specifies the row number at which to start parsing text from the text file. The default value is 1.

The optional DataType parameter specifies the format of the text in the file and can be one of the following XlTextParsingType constants:

Enum XlTextParsingType xlDelimited = 1 ' Default xlFixedWidth = 2 End Enum

The TextQualifier parameter is the text qualifier. It can be one of the following

XlTextQualifier constants:

Enum XlTextQualifier xlTextQualifierNone = -4142

xlTextQualifierDoubleQuote = 1 ' Default xlTextQualifierSingleQuote = 2 End Enum

The ConsecutiveDelimiter parameter should be set to True for Excel to consider consecutive delimiters as one delimiter. The default value is False.

There are several parameters that require that DataType be xlDelimited. When any one of these parameters is set to True, it indicates that Excel should use the corresponding character as the text delimiter. They are described here (all default values are False):

Set to True to use the tab character as the delimiter. Semicolon

Set to True to use a semicolon as the delimiter.

Comma

Set to True to use a comma as the delimiter.

Space

Set to True to use a space as the delimiter.

Other

Set to True to use a character that is specified by the OtherChar argument as the delimiter.

When Other is True, OtherChar specifies the delimiter character. If OtherChar contains more than one character, only the first character is used.

The FieldInfo parameter is an array containing parse information for the individual source columns. The interpretation of FieldInfo depends on the value of DataType.

When DataType is xlDelimited, the FieldInfo argument should be an array whose size is the same as or smaller than the number of columns of converted data. The first element of a two-element array is the column number (starting with the number 1), and the second element is one of the following numbers that specifies how the column is parsed:

Value

Description

1

General

2

Text

3

MDY date

4

DMY date

5

YMD date

รณ

MYD date

7

DYM date

8

YDM date

9

Skip the column

If a two-element array for a given column is missing, then the column is parsed with the General setting. For instance, the following value for FieldInfo causes the first column to be parsed as text and the third column to be skipped:

All other columns will be parsed as general data.

To illustrate, consider a text file with the following contents:

"John","Smith","Serial Record",1/2/98 "Fred","Gwynn","Serials Order Dept",2/2/98 "Mary","Davis","English Dept",3/5/98 "David","Johns","Chemistry Dept",4/4/98

The code:

Workbooks.OpenText

FileName:="d:\excel\temp.txt", Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,

TextQualifier:=xlTextQualifierDoubleQuote,

ConsecutiveDelimiter:=True,

Comma:=True,

FieldInfo:=Array(Array(1, 2),

produces the worksheet shown in Figure 17-2. Note that the cells in column D are formatted as dates.

Figure 17-2. A comma-delimited text file opened in Excel

A.

B

c

D

1

John

Smith

Serial P^oord

1/2/98

2

Fred

Gwynn

Serials Ordoi Dept

2/2/98

3

Mary

Davis

English Dept

3/5/98

4

Dari'id

Joh^s

Chemistry Dept

4/4/98

On the other hand, if DataType is xlFixedWidth, the first element of each two-element array specifies the starting character position in the column (0 being the first character) and the second element specifies the parse option (1-9) for the resulting column, as described earlier.

To illustrate, consider the text file whose contents are as follows:

0-125-689

2-523-489

3-424-664

4-125-160

The code:

Workbooks.OpenText

FileName:="d:\excel\temp.txt", Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2),

Array(1, 9), Array(2, 2), Array(5, 9), Array(6, 2))

produces the worksheet in Figure 17-3. (Note how we included arrays to skip the hyphens.) Figure 17-3. A fixed-width text file opened in Excel

Finally, it is important to observe that the text file is opened in Excel, but not converted to an Excel workbook file. To do so, we can invoke the SaveAs method, as in:

Application.ActiveSheet.SaveAs

FileName:="d:\excel\temp.xls", FileFormat:=xlWorkbookNormal

+7 -1

Responses

  • Nina
    How to use OpenText vba?
    7 years ago

Post a comment