Sub GenerateTxtFile()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim filePath As String
    Dim txtData As String
    
    Set ws = ActiveSheet ' Change to the specific worksheet if needed
    
    ' Get the path of the Excel file
    filePath = ThisWorkbook.Path & "\RxH.txt"
    
    With ws
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        ' Loop through each row and construct the TXT data
        For i = 2 To lastRow ' Start from row 2 to skip the headings row
            Dim invoiceNo As String
            invoiceNo = .Cells(i, "C").Value
            Dim dashIndex As Long
            dashIndex = InStr(1, invoiceNo, "-")
            
            txtData = txtData & _
                Replace(.Cells(i, "A").Value, ",", "") & "|" & _
                "R1" & "|" & _
                Left(invoiceNo, dashIndex - 1) & "|" & _
                Mid(invoiceNo, dashIndex + 1) & "|" & _
                Format(.Cells(i, "B").Value, "dd/mm/yyyy") & "|" & _
                Replace(.Cells(i, "D").Value, ",", "") & vbCrLf ' vbCrLf adds a new line
        Next i
    End With
    
    ' Write the TXT data to a file
    Open filePath For Output As #1
    Print #1, Mid(txtData, 1, Len(txtData) - 2) ' Exclude the last vbCrLf
    Close #1
    
    MsgBox "TXT file generated successfully!", vbInformation
End Sub

Sub GenerateTxtFile()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim filePath As String
    Dim txtData As String
    Set ws = ActiveSheet ' Switch to the specific sheet if necessary
    ' Gets the path to the Excel file
    filePath = ThisWorkbook.Path & "\RxH.txt"
    With ws
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' Iterate through each row and build the TXT data
        
        For i = 2 To lastRow ' Start from row 2 to skip the row of headers
            Dim invoiceNo As String
            invoiceNo = .Cells(i, "C").Value
            Dim dashIndex As Long
            dashIndex = InStr(1, invoiceNo, "-")
            txtData = txtData & _
                Replace(.Cells(i, "A").Value, ",", "") & "|" & _
                "R1" & "|" & _
                Left(invoiceNo, dashIndex - 1) & "|" & _
                Mid(invoiceNo, dashIndex + 1) & "|" & _
                Format(.Cells(i, "B").Value, "dd/mm/yyyy") & "|" & _
                Replace(.Cells(i, "D").Value, ",", "")
            ' Adds a new line only if it is not the last row
            
            If i < lastRow Then
                txtData = txtData & vbCrLf ' vbCrLf add a new line
            End If
        Next i
    End With
    ' Writes TXT data to a file
    Open filePath For Output As #1
    Print #1, txtData ' Excludes the last extra line
    Close #1
    MsgBox "TXT file generated successfully!", vbInformation
End Sub