Saturday, May 10, 2025

VBA Delete lines which contains date in specific format

VBA code to delete all rows from a text file which contains date format like Jan 09, 2025. The code should also check if line begins with single tab before the date.
Sub DeleteLinesWithSpecificDateFormat()
    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:\Users\ajeet\Desktop\meta\meta.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(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) \d{2}, \d{4}"
        .IgnoreCase = True
        .Global = False
    End With

    ' Read each line and write to temp file if it doesn't match
    Do Until inputFile.AtEndOfStream
        line = inputFile.ReadLine
        If Not regex.Test(line) Then
            outputFile.WriteLine line
        End If
    Loop

    inputFile.Close
    outputFile.Close

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

    MsgBox "Lines removed successfully!", vbInformation
End Sub

No comments:

Post a Comment

Hot Topics