Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Opening text file, fixed width macro?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •