Automate Excel Text Import Wizard?

Question

I receive files on occasion in a fixed width format. I need to import them into Excel but Excel doesn't perfectly pick up the columns. I can do it manually each time with the Text Import Wizard, but I'm wondering if there is a way to create a "text import template" or something similar - since these files are always the same format.

Answer 1

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.

Answer 2

If you are fine with some Perl programming,
there is a Spreadsheet module which supports WriteExcel.

I have used this earlier to do the inverse of what you want -- convert an Excel file to CSV format,
I used ReadExcel for that.

Take a look at the examples in the WriteExcel link here and other references for XLS-to-CSV on that question, you might find something that works for you between the two.