Based on the code below I make changes to the "s.txt" file.Is there a way to do this for every txt file in the folder regardless of the file name?

Viewed 40
Sub Macro3()

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String

' Edit as needed
sFileName = "M:\s.txt"

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
    Line Input #iFileNum, sBuf
    sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

sTemp = Replace(sTemp, ",", ".")

iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum

End Sub
2 Answers

Please, try the next adapted code. It uses Dir to find all 'txt' files in the defined folder path:

Sub Macro3_AllTxtFiles()
 Dim sBuf As String, sTemp As String, iFileNum As Integer, sFileName As String
 Dim folderPath As String

 folderPath = "M:\"

 sFileName = Dir(folderPath & "*.txt") 'first text file name (without the folder path...)

 Do While sFileName <> ""
        sTemp = "" 'initialize the variable for each iteration
        iFileNum = FreeFile
        Open folderPath & sFileName For Input As iFileNum
                Do Until EOF(iFileNum)
                    Line Input #iFileNum, sBuf
                    sTemp = sTemp & sBuf & vbCrLf
                Loop
        Close iFileNum
        
        sTemp = Replace(sTemp, ",", ".")
        
        iFileNum = FreeFile
        Open folderPath & sFileName For Output As iFileNum
            Print #iFileNum, sTemp
        Close iFileNum
        
        sFileName = Dir
  Loop
End Sub

Apply to All Files in a Folder

Option Explicit

Sub ProcessFolder()

    Const FolderPath As String = "C:\Test"
    Const FilePattern As String = "*.txt"
    
    ' Make sure the folder path ends with the path separator.
    Dim sPath As String: sPath = FolderPath
    If Right(sPath, 1) <> Application.PathSeparator Then
        sPath = sPath & Application.PathSeparator
    End If
    
    ' Write the first file name to a variable ('sName').
    Dim sName As String: sName = Dir(sPath & FilePattern)
    
    ' Validate the first file name.
    If Len(sName) = 0 Then
        MsgBox "No files found.", vbExclamation
        Exit Sub
    End If
    
    ' Declare variables.
    Dim FilePath As String
    Dim ErrNum As Long
    Dim tfCount As Long ' total
    Dim pfCount As Long ' processed
    
    ' Process each file using the 'ReplaceCommasWithDots' procedure.
    Do While Len(sName) > 0
        tfCount = tfCount + 1
        FilePath = sPath & sName
        On Error Resume Next
            ReplaceCommasWithDots FilePath
            ErrNum = Err.Number
        On Error GoTo 0
        If ErrNum > 0 Then ErrNum = 0 Else pfCount = pfCount + 1
        sName = Dir
    Loop

    ' Inform (with stats).
    MsgBox "Files processed: " & pfCount & "(" & tfCount & ")", vbInformation

End Sub

Sub ReplaceCommasWithDots(ByVal FilePath As String)

    Dim TextFile As Long: TextFile = FreeFile
    
    Dim TextString As String
    
    Open FilePath For Input As TextFile
        TextString = Input(LOF(TextFile), TextFile)
    Close TextFile
    
    TextString = Replace(TextString, ",", ".")
    
    Open FilePath For Output As TextFile
        Print #TextFile, TextString
    Close TextFile

End Sub
Related