The Open Text Function

Workbooks.OpenText is the VBA equivalent of opening a text file in Excel by using Office Menu O Open. It opens the text file, parses it to identify numbers, dates, Booleans, and strings, and stores the results in worksheet cells. Of relevance to this chapter is the method Excel uses to parse the data file (and how it has changed over the past few versions).

In Excel 5, the text file was parsed according to your Windows Regional Settings when opened from the user interface, but according to U.S. formats when opened in code. In Excel 97, this was changed to always use these settings from both the UI and code. Unfortunately, this meant that there was no way to open a U.S.-formatted text file with any confidence that the resulting numbers were correct. Since Excel 5, you have been able to specify the date order to be recognized, on a column-by-column basis, which works very well for numeric dates (for example, 01/02/2007).

Excel 2000 introduced the Advanced button on the Text Import Wizard, and the associated DecimalSeparator and ThousandsSeparator parameters of the OpenText method. These parameters allow you to specify the separators that Excel should use to identify numbers, and they are welcome additions. It is slightly disappointing to see that you cannot specify the general date order in the same way:

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

While Microsoft is to be congratulated for fixing the number format problems in Excel 2000, further congratulations are due for fixing the problem of month and day names in Excel 2002, and for providing a much tidier alternative for distinguishing between U.S.-formatted and locally formatted text files.

Prior to Excel 2002, the OpenText method would only recognize month and day names according to the Windows Regional Settings, and date orders had to be specified for every date field that wasn't in MDY order. In Excel 2002, the OpenText method was given a Local parameter, with which you can specify whether the text file being imported uses U.S. English formatting throughout, or whether it uses locally formatted dates, numbers, and so on:

□ If Local:=True, Excel will recognize numbers, dates, and month and day names according to the Windows Regional Settings (and the Override DecimalSeparator and ThousandsSeparator separators, if set).

□ If Local:=False, Excel will recognize numbers, dates, and month and day names according to standard U.S. English settings.

In either case, the extra parameters of DecimalSeparator, ThousandsSeparator, and Fieldlnfo can be used to further refine the specification (overriding the Local parameter's defaults).

0 0

Post a comment