Guru do Excel e Power BI

 ExpExcelTxT()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim DataTextStr As String

ListSep = "|"
Set SrcRg = ActiveSheet.UsedRange

Open "C:\Users\Carlos André\Desktop\ArqExp.txt" For Output As #1

For Each CurrRow In SrcRg.Rows
    CurrTextStr = ""
    For Each CurrCell In CurrRow.Cells
        CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
    Next
    While Right(CurrTextStr, 1) = ListSep
        CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
    Wend
    CurrTextStr = CurrTextStr & ListSep
    Print #1, CurrTextStr
Next
Close #1
End Sub

 ...  
 While Right(CurrTextStr, 1) = ListSep
        CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
    Wend
....