Solved

Set column breaks programmatically when importing a file

Posted on 2014-02-25
1
114 Views
Last Modified: 2014-03-10
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
Comment
Question by:dbfromnewjersey
1 Comment
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39885668
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now