We simply tell Excel
we??™re connecting to a text file, and then provide the path to the file and add it to the
QueryTables collection via the Add method.
Then there are some common properties, such as the FillAdjacentFormulas and
SavePassword properties. After the RefreshPeriod property, we begin to see a lot of text
file??“specific commands. We can set properties that define the type of text file we??™re working
with by setting the TextFileParse type to xlFixedWidth if our data is arranged in columns
of fixed widths, or xlDelimited if we have a character-delimited file. If we set this to
xlDelimited, we can then set one or more of the following properties to True:
??? TextFileTabDelimiter
??? TextFileSemicolonDelimiter
CHAPTER 2 n DATA IN, DATA OUT 52
??? TextFileCommaDelimiter
??? TextFileSpaceDelimiter
TextFileColumnDataTypes Property
The Macro Recorder generated this line of code:
.TextFileColumnDataTypes = Array(2, 2, 2)
Setting this property to 2 for all columns tells Excel to format the columns as text. These
values correspond to the xlTextFormat constant in Table 2-2. If you enter more values into
this array than there are columns in your data, the additional values are ignored. To see the
numeric equivalent for Excel constants like these, type the name into the Immediate window
(go to View ?¤ Immediate Window or press Ctrl+G) in the VBE, preceded by the ? output character.
You can use the xlColumnDataType constants listed in Table 2-2 to specify the column
data types used or the actions taken during a data import.
Pages:
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79