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

Implementing VBA macro is the most effective, quickest, and safest method to run any operation in Excel. This article will show you how to convert contents from an Excel worksheet to a pipe-delimited text file with the VBA macro.

What Is Pipe Delimited Text File?

A delimiter is a character that specifies the boundary between independent strings. Commas, parentheses, brackets etc. are delimiter symbols.
The symbol “|” is called “pipe”. When a text file holds information that is organized in a structural way separated by the pipe symbol is called a pipe delimited text file. The ASCII code of the pipe symbol (“|”) is 124.

3 Macro Implementations to Convert Excel Sheet to Pipe Delimited Text File

With the following dataset as the example of this article, we will show you how to convert contents from an Excel sheet to a pipe-delimited text file in 3 different methods with VBA macro.

1. Macro to Transfer Contents from Excel Sheet to a Pipe Delimited Text File in the Active Folder

This section will show you how to transfer the contents from an Excel spreadsheet to a pipe delimited text file and auto-save the file in the active folder with the VBA macro.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Next, in the pop-up code window, from the menu bar, click Insert -> Module.

• Then, 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 or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the successful code execution, look at the image below to find out the result.

As a result, there is a newly created text file named “Student Information” (the name was provided in the code) in the active folder. It carries the exact contents from the Excel sheet where the values from each cell are separated with pipe delimiters in the text file.

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

2. VBA Macro to Export Excel Contents with Specified Range to a Pipe Delimited Text File

The previous section converted all the contents from an Excel worksheet to a pipe-delimited text file. This section will teach you how to export content from a specified range in an Excel sheet with macro.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, 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.

• After that, Run the macro as we showed you in the above section. The result is shown in the image below.

As a result, there is 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, then you must 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

3. Macro to Convert Excel to a Pipe Delimited Text File with User-Defined Name in User-Defined Folder

The previous two sections automatically save the newly created text file in the system-defined folder. But from this section, you will learn not only to convert an Excel sheet to a pipe delimited text file but also how to name the file from the user end and how to save the file in the user-defined folder with the VBA macro.

Steps:

• As previously shown, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, 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, there will be 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 as well. Also, you can save the file anywhere in your system.

• Once you save the file, let’s open it to check whether the contents from the Excel sheet really exported in the text file or not.

As you can see from the image above, there is 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 Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you 3 different methods on how to convert contents from an Excel worksheet to a pipe delimited text file with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.