Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 125
  • Last Modified:

Set column breaks programmatically when importing a file

I have a text file which is effectively a report that I need to import into Excel. In addition to the records I need, the file has a lot of other garbage (unneeded records, headers, page numbers, summary totals, etc).

Because this is a routine I have to do often, I'm getting tired of having to make manual adjustments in the text import wizard window. Plus, it's very time consuming.

Regarding the records I'm interested in, the data I need is always in the same position in the source file. What I mean by that is that (from left to right), I need bytes 1 thru 10 to be its own column, bytes 50 thru 59 to be its own column and bytes 60 thru 69 to be  its own column.  I don't care about any of the other data.  It's ok if it gets imported into one or more other columns or not. I can get rid of the unnecessary data later on in a clean up procedure.  Is there a macro that can accomplish this?  Thanks.
0
dbfromnewjersey
Asked:
dbfromnewjersey
1 Solution
 
Rgonzo1971Commented:
Hi

pls try

    Workbooks.OpenText Filename:= _
        "c:\YourFile.txt", Origin:= _
        xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
        1), Array(10, 9), Array(50, 1), Array(59, 9), Array(60, 1), Array(69, 9)), _
        TrailingMinusNumbers:=True

Open in new window

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now