Info

Step 3 of the wizard is identical to step 3 for a fixed-width file. In this case, specify that the third column has a date format. Click Finish, and you will have this code:

Workbooks.OpenText Filename:="C:\sales.txt", Origin:=437, _

StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ Array(3, 3), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _ Array(8, 1)), TrailingMinusNumbers:=True

Although this code appears longer, it is actually far simpler. In the FieldInfo parameter, the two element arrays consist of a sequence number (starting at 1 for the first field) and then an xlColumnDataType from Table 18.1. In this example, Array(2, 1) is simply saying "the second field is of general type." Array(3, 3) is saying, "the third field is a date in M-D-Y format." The code is longer because they explicitly specify each possible delimiter is set to False. Because False is the default for all delimiters, you really need only the one that you will use. The following code is equivalent:

Workbooks.OpenText Filename:="C:\sales.txt", DataType:=xlDelimited, Comma:=True, _ FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 3), Array(4, 1), _ Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1))

Finally, to make the code more readable, you can use the constant names instead of the code numbers.

Workbooks.OpenText Filename:="C:\sales.txt", DataType:=xlDelimited, Comma:=True, _ FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlGeneralFormat), _ Array(3, xlMDYFormat), Array(4, xlGeneralFormat), Array(5, xlGeneralFormat), _ Array(6, xlGeneralFormat), Array(7, xlGeneralFormat), Array(8, xlGeneralFormat))

0 0

Post a comment