VBA Solution for Parsing Information from Text file

[jakeman]

Hi all - I'm looking for a VBA solution to parse information from a text file. The text file is exported out of an old system that formats the data in a kind of report format, not in a table format which would make my life much easier. Currently, the process is that I open the .txt file and search for an account number (#XN12345) and the copy all of the rows attributed to that account number and then paste them into a separate spreadsheet. I've tried a Power Query solution already for this but it doesn't work correctly because my .txt file is not arranged in a table format but in a report format.

So, that being said. My ideal solution would be something like this:

1. Search for account number "XN12345"
2. When found, return the very next row of data all the way down to the break in the line for that account which takes place where the text reads "Totals for detail entries."
3. Copy the entire range of values and return them to a new spreadsheet

Any help provided would be appreciated.

Thank you!

 

[Bosquedeguate]

Hi all - I'm looking for a VBA solution to parse information from a text file. The text file is exported out of an old system that formats the data in a kind of report format, not in a table format which would make my life much easier. Currently, the process is that I open the .txt file and search for an account number (#XN12345) and the copy all of the rows attributed to that account number and then paste them into a separate spreadsheet. I've tried a Power Query solution already for this but it doesn't work correctly because my .txt file is not arranged in a table format but in a report format.

So, that being said. My ideal solution would be something like this:

1. Search for account number "XN12345"
2. When found, return the very next row of data all the way down to the break in the line for that account which takes place where the text reads "Totals for detail entries."
3. Copy the entire range of values and return them to a new spreadsheet

Any help provided would be appreciated.

Thank you!

Please provide an example of your text file.