Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    VBA for Text-to-Columns

    I run a report from a legacy system that only displays results to a txt file. I can copy-paste the results into one column of an Excel spreadsheet and then run the Text-to-Columns feature to parse out the results. But because it's from a print file the report's page headings appear and cause the column-breaks in text-to-columns to require adjustment.

    I've worked out the column break positions across the ruler. How can I get these values into a macro?

    The values for the column breaks are: 33, 40, 49, 60, 69, 76, 80, 97, 114, 116, 133, 150, 167, 184

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Can you import the text file and use space as the field delimiter? Then you won't need to worry about exact spacing.
    Can you post a sample of the data?

    cheers, Paul

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Paul's way is ideal but can you make the headers a permanent part of the worksheet then paste the copied data (minus the header) in the cell below the worksheet header and run Text-to-Columns?

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    Paul T (2016-01-22)

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can specify the positions in the FieldInfo argument - e.g.:
    Code:
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(32, 1), Array(39, 1), Array(48, 1), Array(59, 1), Array(68, 1), Array(75, 1), Array(79, 1), Array(96, 1), Array(113, 1), Array(115, 1), Array(132, 1), Array(149, 1), Array(166, 1), Array(183, 1)), TrailingMinusNumbers:=True
    The first digit of each array is the position, which is zero based.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following User Says Thank You to rory For This Useful Post:

    Arcturus16a (2016-01-22)

  7. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Rory. I got busy with other tasks so I didn't get to try your solution until just now. It appears to be shifted one character but I'm sure that my bad. Anyway, fixing it will help me better understand the code (I hope).

    I would have liked to supply a sample report for others to test but the clients names listed in the report would have had to be scrambled and that would've taken way too long.

  8. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Maudibe & Paul,
    I tried something like that long ago, perhaps it was in Lotus. I used a sequence of dashes and pipes and parsed out the data that way. Looked like this: --------|----|------------|--- and I saved column titles along with the parse line. Formatting the text to a monospace font like Courier helped, too.
    I even reverted to trying that here. I'm just not steady enough to double-click those dang vertical lines to remove without adding a new one here or there and then having to double-click that one, too. And trying to drag them is, well it's a drag. Maybe I need a more precise mouse (if they even exist). But Rory's solution works great.

  9. #7
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts
    HI Rory. Can you tell me what the 2nd parameter is for, or point me to somewhere I can get info on this Array() function. I have got a macro that reads in a very large textfile, and I find that occasionally it doesn't parse properly, so instead of parsing 0.0AB into 2 columns 0.0 and AB, on random rows I am getting 0.0A and B. It doesn't do it for smaller files, only gfor very large ones.

  10. #8
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've found the meaning of the 2nd parameter, and also done some more testing. My problem is definitely due to the size of the file I am reading in. If I read in the whole file (153000 rows) then almost half the rows have the parsing problem. It's not just on one particular field, but mainly where a numeric field is followed by an alpha field. The bad rows are not consecutive. I've found that if I cut the file down to 90000 rows everything is fine, but at 100000 rows I start having problems - at row 45000 - very strange.

    Any explanation for this weird behaviour would be appreciated.

  11. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It's probably better to start your own question for this.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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