If you record a macro (Tools - Macro - Record New Macro) while you open the file, you will get the parameters you need to use the OpenText method. Here's an example
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\dick\My Documents\actsynclog.txt", Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(8, _
1), Array(31, 1), Array(41, 1), Array(57, 1), Array(77, 1), Array(80, 1), Array(82, 1), _
Array(84, 1), Array(86, 1)), TrailingMinusNumbers:=True
The FieldInfo argument is the one you care about. It's an array of 2d arrays. The first element is the column and the second is the data type. This example splits the text file in the following columns: 0, 8, 31, 41, 57, 77, 80, 82, 84, and 86.
With that you, you can write a macro to open the text file. See the GetOpenFileName method of the Application object for how to select which file to open.
Best Answer
You can change the layout or properties for the imported data at any time. On the Data menu, point to Get External Data, and then click either Edit Text Import or Data Range Properties.
If you select Edit Text Import, select the file that you imported originally, and then make changes to the external data in the Text Import Wizard.
By selecting Data Range Properties, you can set query definition, refresh control, and data layout options for the external data.