Opening text file, fixed width macro?

RET79 · Mar 21st, 2002, 08:29 AM

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 · Mar 21st, 2002, 12:55 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 08:29 AM	#1
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	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! --------------------------------------------------------------------------------

Mar 21st, 2002, 12:55 PM	#2
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	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.