Fast Efficient Editing of Text Files

I have to open text files and edit them to remove rows of data, leaving only 6 header rows and the last row.

I also need to apply a multiplier to the values in the last row. The multiplier is held in another part of the Excel file and varies depending on which file I am editing.

This is part of a bigger process.

The challenge I have is that this must be done for multiple files in a very short time period, so I am looking for the quickest way of performing this task.

I have looked at using FSO but my skills are not very strong in that area.

Can you provide a better solution than the one I am using?

Also, I find the Displayalerts and Close Workbook functionality is inconsistent - sometimes dialogue boxes still appear. Can you provide some hints in this area?

Files attached:
A cut down version of the Excel workbook
Before and after examples of the text file

Please Login or Register  to view this content.

Thanks!

Hi Labrooy,

This sounds like a job for Power Query that is a newer tool in Excel 2010 and newer. It pulls in lots of different types of files and can do things like keep the top 6 rows and also use filenames or other parts of the workbook to merge in the data. See if you can upgrade and if it sounds like a fit for your problem. It is called Get & Transform in Excel 2016 but was called Power Query before that.

http://blogs.adatis.co.uk/dan%20evan...in-Power-Query or
http://www.excelguru.ca/blog/2014/12...s-a-text-file/

This code is only for testing purpose.
See if the code generates the new text file.

Please Login or Register  to view this content.