Using Excel Macro Recorder

May 28, 2003 - by Bill Jelen

Kevin asks,

I have a set of data that is stored in a tab delimited text file. Some of the information needs to retain the “padding” - such as Zip Codes (they need to be 5 or 9 characters in length). When the text file is opened into excel, the Text Import Wizard automatically comes up. There is an option to specify that the column containing that data is Text rather than Generic. Selecting this option works – but it requires me to do the open manually. I have not been able to locate a way in VBA to have the information be Text rather than Generic – and saving my ‘padding’.

Turn on the macro recorder and record the process of importing your text file. Here is the resulting code:

Workbooks.OpenText Filename:= “C:TestData.txt”, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 1)), TrailingMinusNumbers:=True

The FieldInfo parameter controls how each field is imported. In this case, the macro recorder noted that you have 4 fields. The Array(2, 1) entry says that for the 2nd field, use a “General” field type. Note that for field 3, the entry is Array(3, 2). The “2” is the code for Excel to import this field as Text.