Fast Efficient Editing of Text Files

[labrooy]

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

Sub Example()
'Clear the file contents except the 6 header rows and last row
    Dim strFileName As String, strFilePath As String
    Dim x As Long, y As Long, wb As Workbook, MK As Integer
 
    Application.ScreenUpdating = False
        
' Define the file name and file path
    strFileName = "Sample file.txt"
    strFilePath = "C:\Work\"
    MK = "40"				' This is a variable, hard coded here to simplify the process
    
' Open text file
    Set wb = Workbooks.Open(Filename:=strFilePath & strFileName)
    If Not wb Is Nothing Then wb.Activate
    
        y = Cells(Rows.Count, "A").End(xlUp).Row - 1
        x = Cells(6, Columns.Count).End(xlToLeft).Column
 
' Remove rows of data and apply multiplier
   If y > 6 Then
    Rows("7:" & y).EntireRow.Delete
    Range("C8").Value = Range("C7").Value * MK
    Range("D8").Value = Range("D7").Value * MK
    Range("E8").Value = Range("E7").Value * MK
    Range("F8").Value = Range("F7").Value * MK
    Range("C7:F7") = Range("C8:F8").Value
    Rows("8").EntireRow.Delete
    End If
  
' Save workbook and close
    Application.DisplayAlerts = False
    ActiveWorkbook.Save

    '   Close Workbook
    ActiveWorkbook.Close SaveChanges:=True
    Application.DisplayAlerts = True
  
Application.ScreenUpdating = True

End Sub

Thanks!