Automatic Text Files

Now that you have reviewed how to open text files manually, opening text files programmatically isn't much of a stretch. To open text files programmatically, use the OpenText method of the Workbooks object. The syntax of OpenText is as follows.

Application.Workbooks.OpenText Filename, [Origin], _ [StartRow], [DataType], [TextQualifier], _ [ConsecutiveDelimiter], [Tab], [Semicolon], [Comma], _ [Space], [Other], [OtherChar], [FieldInfo], _ [TextVisualLayout], [DecimalsSeparator], _ [ThousandsSeparator], [TrailingMinusNumbers], [Local]

As you can see, OpenText has quite a few parameters—more than any other method you have seen so far. The parameters are explained in the following list. In order to conserve some space, I have combined related parameters.

Filename This is the only required parameter. Obviously this is used to tell OpenText which file to open.

Origin Origin is an optional parameter that specifies either the file origin (or platform) or the code page used to encode the text file (such as OEM United States, Korean, or Nordic). If you need to specify the platform used, you can use one of the xlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. To specify a particular code page, you need to pass in the integer value associated with the desired code page. The best way to figure out the value is to find the code page you need in the file origin drop-down list of step 1 (Figure 15.3) of the Text Import Wizard; for example, the integer value associated with the OEM United States code page is 437.

StartRow StartRow is an optional parameter that specifies the row in the text file to begin importing. The default value is 1.

DataType DataType is an optional parameter that specifies how columns are organized in the text file. You can use one of the xlTextParsingType constants: xlDelimited or xlFixedWidth. By default, Excel attempts to determine the type when it opens the file.

TextQualifier This is an optional parameter that specifies how text is qualified in the file. You can use one of the xlTextQualifier constants: xlTextQualifierDoubleQuote, xlTextQualifier-None, or xlTextQualifierSingleQuote. The default value is xlTextQualifierDoubleQuote.

ConsecutiveDelimiter This is an optional parameter that instructs Excel how to handle consecutive delimiters. Use true to instruct Excel to treat consecutive delimiters as a single delimiter. The default value is false.

Tab, Semicolon, Comma, and Space These optional parameters are only relevant if the DataType parameter is xlDelimited. Set the desired parameter to true to have Excel consider the character a valid delimiter in the text file. The default value for all of these parameters is false.

Other and OtherChar If the text file is delimited by a character other than a tab, semicolon, comma, or space (for example, I am partial to the pipe character "|") you can use these two optional parameters to specify that a different character is used (Other = true) and what the character (OtherChar = "|") is. Note that OtherChar is required if Other is true. By default Other is false.

FieldInfo Fieldlnfo is technically an optional parameter; however, if the file you are opening is a fixed-width text file, you might as well consider this a required parameter because you will use it to instruct Excel on where each column starts. FieldInfo is also used to indicate each column's data type. As you may have presumed, FieldInfo is a little more complicated than your typical parameter. FieldInfo should be an array of two element arrays. The interpretation of the array depends on the value of the DataType parameter.

If DataType = xlDelimited than the first element represents the column number and the second element represents the column's data type. To specify a data type, use one of the xlColumn-DataType constants: xlGeneralFormat, xlTextFormat, xlSkipColumn, xlMDYFormat, xlDMY-Format, xlYMDFormat, xlMYDFormat, xlDYMFormat, xlYDMFormat, or xlEMDFormat (applicable only if you have installed and selected Taiwanese language support).

If DataType = xlFixedWidth than the first element specifies the starting character of the column and the second element specifies the column's data type.

TextVisualLayout An optional parameter that specifies the visual layout of the text.

DecimalsSeparator, ThousandsSeparator, and TrailingMinusNumbers These optional parameters specify the character used as a decimal separator or thousands separator and whether negative numbers have a trailing minus sign.

Local This is a mysterious optional parameter for which there isn't any documentation.

The parameters of OpenText are quite a mouthful. Generally I only use a handful of them; therefore, I find it is easier to wrap this method up in my own function. I have one function for opening delimited files and another for opening fixed-width files. Listing 15.1 presents the function for opening delimited files along with a procedure for testing it.

+1 0

Post a comment