Saturday, May 10, 2025

VBA Replace lines which contains date in specific format with specific text

VBA code to replace all rows from a text file which contains date format like Jan 09, 2025 with text ANSWER. The code should also check if line begins with single tab before the date.
Sub ReplaceLinesWithSpecificDateFormat()
    Dim fso As Object
    Dim inputFile As Object
    Dim outputFile As Object
    Dim filePath As String
    Dim tempPath As String
    Dim line As String
    Dim regex As Object

    ' Change this to your actual file path
    filePath = "C:\Path\To\Your\File.txt"
    tempPath = filePath & ".tmp"

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set inputFile = fso.OpenTextFile(filePath, 1) ' ForReading
    Set outputFile = fso.CreateTextFile(tempPath, True) ' Overwrite

    ' Create RegExp to match lines starting with tab and a date like May 09, 2025
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Pattern = "^\t(January|February|March|April|May|June|July|August|September|October|November|December) \d{2}, \d{4}"
        .IgnoreCase = True
        .Global = False
    End With

    ' Read each line and write to temp file, replacing matching lines with "ANSWER"
    Do Until inputFile.AtEndOfStream
        line = inputFile.ReadLine
        If regex.Test(line) Then
            outputFile.WriteLine "ANSWER"
        Else
            outputFile.WriteLine line
        End If
    Loop

    inputFile.Close
    outputFile.Close

    ' Replace original file with modified content
    fso.DeleteFile filePath
    fso.MoveFile tempPath, filePath

    MsgBox "Matching lines replaced with 'ANSWER'!", vbInformation
End Sub

No comments:

Post a Comment

Hot Topics