Macro to Convert Excel to Pipe Delimited Text File (3 Methods)

We have the following dataset. The contents of the dataset are relatively irrelevant to the code we’ll use.

Method 1 – Macro to Transfer Contents from an Excel Sheet to a Pipe-Delimited Text File in the Active Folder

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer and select Visual Basic to open the Visual Basic Editor.

• From the menu bar, click Insert and select Module.

• Copy the following code and paste it into the code window.

Sub ExcelToPipeDelimitedText()
    Const Delimiter As String = "|"
    Dim iSheet As Worksheet
    Set iSheet = ActiveSheet
    Dim iRow As Long
    Dim iCol As Long
    Dim x As Long
    Dim y As Long
    iRow = iSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    iCol = iSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Dim FilePath As String
    FilePath = ThisWorkbook.Path & "\"
    Dim FileName As String
    FileName = FilePath & "Student Information.txt"
    Dim iObj As Object
    Set iObj = CreateObject("ADODB.Stream")
    iObj.Type = 2
    iObj.Charset = "unicode"
    iObj.Open
    Dim z() As Variant
    ReDim z(1 To iCol)
        For x = 1 To iRow
            For y = 1 To iCol
                z(y) = Chr(34) & iSheet.Cells(x, y).Text & Chr(34)
            Next
        iObj.WriteText Join(z, Delimiter), 1
        Next
    iObj.SaveToFile FileName, 2
    Dim TextApp
    TextApp = Shell("C:\WINDOWS\notepad.exe " & FileName, 1)
End Sub

Your code is now ready to run.

• Press F5 on your keyboard, select Run and go to Run Sub/UserForm, or click on the small Run icon in the sub-menu bar to run the macro.

Here’s the result.

We get a new text file named “Student Information” (the name was provided in the code under FileName) in the active folder.

Sub ExcelToPipeDelimitedText()

Const Delimiter As String = "|"

Dim iRow As Long
Dim iCol As Long
Dim x As Long
Dim y As Long

iRow = iSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
iCol = iSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Dim FilePath As String
  FilePath = ThisWorkbook.Path & "\"

Dim FileName As String
  FileName = FilePath & "Student Information.txt"

Dim iObj As Object
  Set iObj = CreateObject("ADODB.Stream")
  iObj.Type = 2
  iObj.Charset = "unicode"
  iObj.Open

Dim z() As Variant
  ReDim z(1 To iCol)
   For x = 1 To iRow
      For y = 1 To iCol
         z(y) = Chr(34) & iSheet.Cells(x, y).Text & Chr(34)
      Next
      iObj.WriteText Join(z, Delimiter), 1
   Next
  iObj.SaveToFile FileName, 2

Dim TextApp
  TextApp = Shell("C:\WINDOWS\notepad.exe " & FileName, 1)

End Sub

Read More: How to Write to a Text File Using Excel VBA

Method 2 – VBA Macro to Export Excel Contents within the Specified Range to a Pipe-Delimited Text File

Steps:

• Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy the following code and paste it into the code window.

Public Sub ExcelToPipeDelimitedTextFile()
Const MyDelimiter As String = "|"
Dim iData As Range
Dim iRng As Range
Dim iFile As Long
Dim iResult As String
iFile = FreeFile
Open "Information of Students.txt" For Output As #iFile
    For Each iData In Range("B6:B" & Range("B" & Rows.Count).End(xlUp).Row)
        With iData
            For Each iRng In Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))
                iResult = iResult & MyDelimiter & iRng.Text
            Next iRng
            Print #iFile, Mid(iResult, 2)
            iResult = Empty
        End With
    Next iData
Close #iFile
End Sub

Your code is now ready to run.

• Run the code. The result is shown in the image below.

We get a newly created text file named “Information of Students” (the name was provided in the code). It carries the exact contents from the Excel sheet where the values from each cell are separated with pipe delimiters in the text file.
Note: If you don’t find this newly created “Information of Students” text file in the active folder, look for the file in the system’s main hard disk (usually the C drive in the Windows operating system).

Public Sub ExcelToPipeDelimitedTextFile()

Const MyDelimiter As String = "|"

Dim iData As Range
Dim iRng As Range
Dim iFile As Long
Dim iResult As String

iFile = FreeFile

Open "Information of Students.txt" For Output As #iFile

For Each iData In Range("B6:B" & Range("B" & Rows.Count).End(xlUp).Row)
  With iData
    For Each iRng In Range(.Cells, Cells(.Row, Columns.Count).End(xlToLeft))
      iResult = iResult & MyDelimiter & iRng.Text
    Next iRng
    Print #iFile, Mid(iResult, 2)
    iResult = Empty
  End With
Next iData

Close #iFile

End Sub

Method 8 – Macro to Convert Excel File to a Pipe-Delimited Text File with a User-Defined Name in a User-Defined Folder

Steps:

• Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy the following code and paste it into the code window.

Public Sub CovertToPipeDelimitedTextFile(FileName As String, Delimiter As String, SelectedRange As Boolean, MergeData As Boolean)
    Dim iLine As String
    Dim iFile As Integer
    Dim iRow As Long
    Dim iCol As Integer
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim FirstCol As Integer
    Dim LastCol As Integer
    Dim iValue As String
    Application.ScreenUpdating = False
    On Error GoTo EndMacro:
    iFile = FreeFile
    If SelectedRange = True Then
        With Selection
            FirstRow = .Cells(1).Row
            FirstCol = .Cells(1).Column
            LastRow = .Cells(.Cells.Count).Row
            LastCol = .Cells(.Cells.Count).Column
        End With
    Else
        With ActiveSheet.UsedRange
            FirstRow = .Cells(1).Row
            FirstCol = .Cells(1).Column
            LastRow = .Cells(.Cells.Count).Row
            LastCol = .Cells(.Cells.Count).Column
        End With
    End If
    If MergeData = True Then
        Open FileName For Append Access Write As #iFile
    Else
        Open FileName For Output Access Write As #iFile
    End If
    For iRow = FirstRow To LastRow
        iLine = ""
        For iCol = FirstCol To LastCol
            If Cells(iRow, iCol).Value = "" Then
                iValue = Chr(34) & Chr(34)
            Else
               iValue = Cells(iRow, iCol).Value
            End If
            iLine = iLine & iValue & Delimiter
        Next iCol
        iLine = Left(iLine, Len(iLine) - Len(Delimiter))
        Print #iFile, iLine
    Next iRow
EndMacro:
    On Error GoTo 0
    Application.ScreenUpdating = True
    Close #iFile
End Sub
Sub ExcelFileToPipeDelimitedText()
    Dim FileName As Variant
    Dim Delimiter As String
    FileName = Application.GetSaveAsFilename(InitialFileName:="C:\ExcelDemy\" & Format(Now(), "DD-MM-YYYY-") & Range("C4").Text & "-Information", FileFilter:="Text Files (*.txt),*.txt")
    If FileName = False Then
        Exit Sub
    End If
    Delimiter = "|"
    If Delimiter = vbNullString Then
        Range("B1").Select
        Delimiter = "|"
    End If
    If Delimiter = vbNullString Then
        Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Delimiter: " & Delimiter
        CovertToPipeDelimitedTextFile FileName:=CStr(FileName), Delimiter:=CStr(Delimiter), SelectedRange:=False, MergeData:=True
End Sub

Your code is now ready to run.

• After you Run this piece of code, you’ll get a pop-up Save As system window.
• The name for the text file you provided in the code will be displayed in the File name. You can change the file name from here.
• You can save the file anywhere in your system.
• Press Save when you enter the name and go to the folder you want.

• Here’s the result.

We get a newly created text file named “17-05-2022-Student Name-Information” (the name was provided in the code). It carries the exact contents from the Excel sheet where the values from each cell are separated with pipe delimiters in the text file.

Public Sub CovertToPipeDelimitedTextFile(FileName As String, Delimiter As String, SelectedRange As Boolean, MergeData As Boolean)

Dim iLine As String
Dim iFile As Integer
Dim iRow As Long
Dim iCol As Integer
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Integer
Dim LastCol As Integer
Dim iValue As String

Application.ScreenUpdating = False

On Error GoTo EndMacro:

iFile = FreeFile
  If SelectedRange = True Then
    With Selection
      FirstRow = .Cells(1).Row
      FirstCol = .Cells(1).Column
      LastRow = .Cells(.Cells.Count).Row
      LastCol = .Cells(.Cells.Count).Column
    End With
  Else
    With ActiveSheet.UsedRange
      FirstRow = .Cells(1).Row
      FirstCol = .Cells(1).Column
      LastRow = .Cells(.Cells.Count).Row
      LastCol = .Cells(.Cells.Count).Column
    End With
  End If

If MergeData = True Then
   Open FileName For Append Access Write As #iFile
Else
   Open FileName For Output Access Write As #iFile
End If

For iRow = FirstRow To LastRow
    iLine = ""
    For iCol = FirstCol To LastCol
       If Cells(iRow, iCol).Value = "" Then
          iValue = Chr(34) & Chr(34)
       Else
         iValue = Cells(iRow, iCol).Value
       End If
         iLine = iLine & iValue & Delimiter
    Next iCol
    iLine = Left(iLine, Len(iLine) - Len(Delimiter))
    Print #iFile, iLine
Next iRow

EndMacro:
  On Error GoTo 0

Application.ScreenUpdating = True

Close #iFile

End Sub

Sub ExcelFileToPipeDelimitedText()

Dim FileName As Variant
Dim Delimiter As String

FileName = Application.GetSaveAsFilename(InitialFileName:="C:\ExcelDemy\" & Format(Now(), "DD-MM-YYYY-") & Range("C4").Text & "-Information", FileFilter:="Text Files (*.txt),*.txt")

If FileName = False Then
    Exit Sub
End If
    Delimiter = "|"
If Delimiter = vbNullString Then
    Range("B1").Select
    Delimiter = "|"
End If
If Delimiter = vbNullString Then
    Exit Sub
End If

Debug.Print "FileName: " & FileName, "Delimiter: " & Delimiter
  CovertToPipeDelimitedTextFile FileName:=CStr(FileName), Delimiter:=CStr(Delimiter), SelectedRange:=False, MergeData:=True

End Sub

Download the Practice Workbook