Opening text file, fixed width macro?

[RET79]

I get the format required for opening a text file in a table on an excel spreadsheet like this:

Category Field start Width
AGE 0 3
POL NO 3 6
DATE 9 6
PREM_TYPE 15 5
POL_TYPE 20 10
UNIT1 30 4
UNIT2 34 2
UNIT3 36 6
END_DATE 42 6
48

I get this code in my macro, which I would like to generalise and be updated automatically based on the table above:

Workbooks.OpenText Filename:="C:My Documentsfile.txt", Origin:=xlWindows _
, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(9, 1), Array(15, 1), Array(20, 1), Array(30, 1), Array(34, 1), Array(36, 1),Array(42, 1), Array(48, 1))

So I would like to get this code updated automatically for any given change in the column headings and widths given in the table on the excel spreadsheet. I just want to put the different column headings and widths in the spreadsheet, and just click a button to open the text file in exactly that format.

I would be grateful for any help. Thankyou!


--------------------------------------------------------------------------------

 

[RET79]

Did anyone have a clue about this message?

I also wanted to point out that I have managed to set up a spreadsheet to generate the code required in the macro by manipulating text strings, IF functions etc.

However, I need to copy the code generated by the spreadsheet (which is just text in the range [D4:D8] say) and paste it over the old code in the module in the VBE everytime. Not a big deal that I know, but doesn't look very professional. Is there any way that you can have a macro where the macro itself 'reads' the text from the range [D4:D8] as code? That would cane so much.