need macro to create pipe delimited text file from excel

You can use this code -

 
Option Explicit
Dim lrow As Long
Dim lcol As Long
Dim txtcsv As String
Dim i As Long
Dim j As Long

Sub create_txt()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

lrow = ThisWorkbook.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
lcol = ThisWorkbook.Worksheets(1).Range("IV1").End(xlToLeft).Column

Workbooks.Add
ActiveWorkbook.SaveAs "Test.txt"

txtcsv = ""

For i = 1 To lrow
    For j = 1 To lcol
        txtcsv = txtcsv & "|" & ThisWorkbook.Worksheets(1).Cells(i, j).Value
        If j = lcol Then
            txtcsv = vbLF & txtcsv & vbLf
        End If
    Next j
    Workbooks("Test.txt").Worksheets(1).Range("A1").Value = txtcsv
Next i

ActiveWorkbook.SaveAs Filename:="Test.txt", FileFormat:=xlText, CreateBackup:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

The vbLF is the line feed character which will copy the data to the next line. I tested it at my end and it works.