Advanced criteria for text file parsing and importing.

[Charleton]

Hello again!

I have a plaintext file conversion of a pdf, of multiple pages - each with a header. I only need the first 20 pages or so of this report but it is about 50. And of those 20 pages, there are three kinds of data I am interested in, "APPLES", "ORANGES", and "GRAPES." The rest of the pages don't get used here, but for illustration, let's say that the next "Section says "BANANAS." The header is the same size for each page and the one row of this header has the information specific to whether I need it to go into a different worksheet.

I am currently able to manually break apart the file for the three sections I need, each a different file, and make .txt files of the three sections I want and import and do heavy formatting to clean those up with vba...., but I am looking to automate this process a bit more by letting vba parse the data imported of choosing what "pages" of the plaintext data from the .txt file need to go to which specific worksheet, "RED", "PURPLE", and "GREEN," respectively, until it gets to a "BANANAS" - at that point, the file should close. I am not sure if this would better for IF Statements or Select Case. I was leaning toward IF Statements originally, but will illustrate this in a Select Case outline. Also, which one would work faster, If or Select Case?

Here is the process.


For Each Line of C:\PRODUCE\FRUIT.TXT

Select Case

For each line of "FRUIT.txt, starting with the fifth line,

CASE .txt file line is "APPLES"
Put on Sheet "RED", and put all lines on sheet "RED" until a line says "GRAPES" or "ORANGES".
CASE .txt file line is "GRAPES"
Put on Sheet "PURPLE", and put all lines on sheet "PURPLE" until a line says "ORANGES" or "APPLES."
CASE .txt file line is "LIMES"
Put on Sheet "GREEN" and put all lines on sheet "GREEN" until a line says "APPLES" or "GRAPES."
CASE .txt file says "BANANAS"
Close "FRUIT.txt" and End Sub


Thanks in advance for any help!

-Charleton

 

[Charleton]

I found the solution in my Mr Excel VBA and Macros Book on Page 398. The text file processing section (Ch 19, starting page 391 is *brilliant*!

I just had to embed the Line Input in cascading if Statements where my criteria are found, and output it to column B.

I can also use these to filter the headers out completely.

I am also going to make a select case version and see if it works faster because I have about 45 text files that 6 people have to do their first hour of the day.

In fact, I found a much slicker and quicker way to do it!

Thanks, Bill and Tracy!