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.


Download Workbook

You can download the free practice Excel workbook from here.


What Is Pipe Delimited Text File?

A delimiter is a character that specifies the boundary between independent strings. Comma, 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.

Macro to Convert Excel to Pipe Delimited Text File

  • 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.

Result of Macro to Convert Excel to Pipe Delimited Text File

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.

VBA Code Explanation

Sub ExcelToPipeDelimitedText()

First, provide a name for the sub-procedure of the macro.

Const Delimiter As String = "|"

Then, specify the pipe delimiter (“|”).

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

Next, declare the necessary variables for the macro.

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

These two lines go through all the values in the rows and the columns of the dataset.

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

After that, specify the active folder as the path for the text file.

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

Then, specify the file name. “Student Information” is the text file name in our case. You can modify the name according to your need.

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

Next, specify the type of the data of the text with the Type property.

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

Later, initiate the FOR Loop to scan through the whole dataset and store the values of the cells in a text file. The values are separated by the delimiter specified in the code.

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

Lastly, specify the path of the application type to convert the Excel contents. We used Notepad as the app.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Apply Macro to Convert Multiple Excel Files to CSV Files


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.

Macro to Convert Range Specified Excel to Pipe Delimited Text File

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

Result of Macro to Convert Range Specified Excel to Pipe Delimited Text File

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 Windows operating system).

VBA Code Explanation

Public Sub ExcelToPipeDelimitedTextFile()

First, provide a name for the sub-procedure of the macro.

Const MyDelimiter As String = "|"

Then, specify the pipe delimiter (“|”).

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

After that, declare the necessary variables for the macro.

iFile = FreeFile

Next, return the file number with the FreeFile function.

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

Later, specify the name of the text file. “Information of Students” is the text file name in our case. You can modify the name according to your need.

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

This piece of code first specifies the range. We started our range to convert from B6 to the last. Then print every value of that range joined by the delimiter in the text file. This is a continuous iteration process until it covers the last cell count of the dataset.

Close #iFile

After finishing printing all the values from the Excel range to the text file separated by the pipe delimiter, this macro line closes the file.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Convert Excel File to Text File with Comma Delimited (3 Methods)


Similar Readings


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.

Macro to Convert Excel to Pipe Delimited User Named Text File

  • 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.

Result of Macro to Convert Excel to Pipe Delimited User Named Text File

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.

VBA Code Explanation

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

First, provide a name for the sub-procedure of the macro. The name of the output file, delimiter, range to convert and the output data are passed inside the parentheses as the arguments of the macro.

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

Then, declare the necessary variables for the macro.

Application.ScreenUpdating = False

Next, turn off the screen updating event.

On Error GoTo EndMacro:

If an error occurs, then go to the EndMacro statement.

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

Next, return the file number with the FreeFile function. First, it will check for any selected range, and then count the rows and columns in the range. Or if there is no selected range then the used range will be the input value here.

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

This piece of code works to generate the output file.

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

Then, it starts iterating through the first row to the last row of the range. It takes the values from each row and column and places them in the specified file separated by the delimiter.

EndMacro:
  On Error GoTo 0

The EndMacro statement. If any error occurs, then return null.

Application.ScreenUpdating = True

Turn back on the screen updating event.

Close #iFile

After finishing placing all the values from the Excel range to the text file separated by the pipe delimiter, this macro line closes the file.

End Sub

Later, end the existing sub-procedure of the macro.

Sub ExcelFileToPipeDelimitedText()

Now, initiate another macro to perform the file naming procedure.

Dim FileName As Variant
Dim Delimiter As String

Next, declare the rest of the necessary variables for the macro.

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

First, we define the path, then define the name with the current date along with the cell value of C4 and the string “Information” with it.

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

This piece of code works with the delimiter and names the file based on it.

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

Convert the contents from the Excel file to the text file. Contents from the Excel file are separated with the delimiter in the text file.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Convert Excel to Comma Delimited CSV File (2 Easy Ways)


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.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo