Microsoft Office Tutorials and References
In Depth Information
Figure 17-2. A comma-delimited text file opened in Excel
YMD date
MYD date
DYM date
YDM date
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:
Array(Array(1, 2), Array(3, 9))
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), _
Array(2, 2), Array(3, 2), Array(4, 6))
produces the worksheet shown in Figure 17-2 . Note that the cells in column D are formatted as
Figure 17-2. A comma-delimited text file opened in Excel
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:
Search JabSto ::

Custom Search