Update txt files using vba

I have a great number of .txt files in a directory which are all listed by name in column A of a spreadsheet and I need to open each of these files and update the fifth line with the data that corrospnds in column B and then save them. *Does anyone have any suggestions as to how i could use VBA to achieve this?

Sub x()

    '// File handle
    Dim iFile As Integer
    
    '// Contains the file contents
    Dim strLines() As String
    
    '// The range to process
    Dim c As Excel.Range
    
    '// Assuming the cell contains the FULL file name including path
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        
        '// Get a free file handle - 99.99% guaranteed to be 1
        iFile = FreeFile
        
        '// Open the file to read contents
        Open (c.Value) For Input As #iFile
        
        '// Read all the text and split using CRLF
        '// Possible delimiters:
        '// vbCRLF
        '// vbCR
        '// vbLF
        strLines = Split(Input(LOF(iFile), iFile), vbCrLf)
        
        '// Finished with the file for the moment
        Close #iFile
        
        '// Replace the line - remember Arrays Start at 0 by default
        '// so the 5th line is element 4
        '// Replace with the contents of the same row, Col B
        strLines(4) = c.Offset(, 1).Value
        
        '// Opening for OUTPUT will truncate the existing file
        Open (c.Value) For Output As #iFile
        
        '// Join the array elements back using the SAME delimiter used
        '// to split them - edit if needed
        Print #iFile, Join(strLines, vbCrLf)
        
        '// finished with this file
        Close #iFile
        
    Next
    
End Sub

Assuming, as mentioned in the code, that the cells contain a fully qualified file name and there are no blank cells.

You should consider adding error handling... If the files contain critical information, you should copy them to a backup directory first - there is no undo facility here.

Sorry to say but it works fine for me... As the error is 'File not Found' it is probably the way you are constructing the filename.

Sub x()

    '// File handle
    Dim iFile As Integer
    
    '// Contains the file contents
    Dim strLines() As String
    
    '// The range to process
    Dim c As Excel.Range
    
    '// Source directory
    Dim strDir As String
    
    '// File to process
    Dim strFile As String
    
    '// Store directory
    strDir = Sheets("Sheet1").Range("C1").Value
    '// Add ending '\' if needed
    strDir = IIf(Right$(strDir, 1) <> "\", strDir & "\", strDir)
    
    '// Check the directory exists.
    If Dir(strDir, vbDirectory) = vbNullString Then
        MsgBox "The directory " & StrConv(strDir, vbUpperCase) & " does not exist. The procedure cannot continue.", vbExclamation, "Error"
        Exit Sub
    End If
    
    '// For each cell in Col A
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        
        '// Ignoring extensions - just get a DIR using the directory and root filename
        strFile = Dir(strDir & c.Value & ".*")
        
        '// If a file (with any extension) exists in the source directory
        If strFile <> vbNullString Then
        
            '// Get a free file handle - 99.99% guaranteed to be 1
            iFile = FreeFile
            
            '// Open the file to read contents
            Open (strDir & strFile) For Input As #iFile
            
            '// Read all the text and split using CRLF
            '// Possible delimiters:
            '// vbCRLF
            '// vbCR
            '// vbLF
            '// Any other character - use literally: ...Split(Input(LOF(iFile), iFile), "|")
            strLines = Split(Input(LOF(iFile), iFile), vbCrLf)
            
            '// Finished with the file for the moment
            Close #iFile
            
            '// Replace the line - remember Arrays Start at 0 by default
            '// so the 5th line is element 4
            '// Replace with the contents of the same row, Col B
            strLines(4) = c.Offset(, 1).Value
            
            '// Opening for OUTPUT will truncate the existing file
            Open (strDir & strFile) For Output As #iFile
            
            '// Join the array elements back using the SAME delimiter used
            '// to split them - edit if needed
            Print #iFile, Join(strLines, vbCrLf)
            
            '// finished with this file
            Close #iFile
        Else
            MsgBox "No file named " & StrConv(c.Value, vbUpperCase) & " exists in the directory " & _
                StrConv(strDir, vbUpperCase), vbExclamation, "Error"
        End If
        
    Next
    
End Sub

This code requires the directory to be added in cell C1. It uses a combination of directory and root filename in Col A to find a file ignoring the extension (I would assume you do not have files named xxx.txt and xxx.csv in the same directory).

It also pops a msgbox if either the directory or any file listed in Col A does not exist...

The choice of cell C1 is purely arbitrary - it can be anywhere, just change the code to suit.

It ignores extensions altogether... As long as the basic file name (without the path or extension) is unique in the directory, it will work.

As small edit to the code in the previous thread. Replace

        strFile = Dir(strDir & c.Value & "*.*")

with

        strFile = Dir(strDir & c.Value & ".*")  '// '*.*' replaced by '.*'

This is just to tighten up the logic a little and make sure only those files listed are found...