Microsoft Office Tutorials and References
In Depth Information
Figure 17-2. A comma-delimited text file opened in Excel
5
YMD date
6
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:
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
dates.
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:
0-125-689
2-523-489
3-424-664
 
 
Search JabSto ::




Custom Search