[Excel VBA] How to Export a Specified Range to a Text File (Tab-Delimited)

There is often a need to export a specific range of Excel data as a “tab-delimited” text file (.txt), especially when importing data into other systems.

Manually copying data and pasting it into Notepad is time-consuming and prone to errors.

In this article, I will explain how to use an Excel VBA macro to easily export a specified range on a sheet as a text file.

The Completed VBA Code

Here is the complete code. You can copy and paste this into a standard module to try it out immediately.

Sub ExportRangeToTextFile()

    ' Declare variables
    Dim exportArea As Range
    Dim tempWb As Workbook
    
    ' --- Configuration Area Start ---
    
    ' Specify the cell range you want to export to text
    ' In this example, we specify range C3 to J15 on the sheet named "DataSheet"
    Set exportArea = ThisWorkbook.Worksheets("DataSheet").Range("C3:J15")
    
    ' --- Configuration Area End ---
    
    ' Prepare to continue processing even if an error occurs (for checking existence)
    On Error Resume Next
    ' Check if the specified sheet or range exists
    If exportArea Is Nothing Then
        MsgBox "The specified sheet or range was not found." & vbCrLf & _
               "Please check the sheet name and cell range in the code.", vbExclamation
        Exit Sub
    End If
    ' Reset error handling
    On Error GoTo 0

    ' Add a new temporary workbook
    Set tempWb = Workbooks.Add
    
    ' Copy the specified range to cell A1 of the first sheet in the new workbook
    exportArea.Copy tempWb.Worksheets(1).Range("A1")
    
    ' Save the new workbook in tab-delimited text format
    ' The file name is "ExportedData.txt" and it is saved in the same folder as this Excel file
    tempWb.SaveAs ThisWorkbook.Path & "\ExportedData.txt", FileFormat:=xlText
    
    ' Close the temporary workbook without saving changes
    tempWb.Close SaveChanges:=False
    
    ' Display completion message
    MsgBox "Export to text file is complete."

End Sub

Detailed Explanation of the Code

Let’s look at what the code is doing step by step.

1. Variable Declaration

Dim exportArea As Range
Dim tempWb As Workbook

First, we prepare “variables” (containers for data) to be used in the macro.

• exportArea: Stores information about the cell range you want to export.
• tempWb: Stores information about the temporary workbook used for saving the text file.

2. Specifying the Output Range

Set exportArea = ThisWorkbook.Worksheets("DataSheet").Range("C3:J15")

Here, you specifically define the range of data to export. The sample code specifies the range from cell C3 to J15 on the sheet named “DataSheet”. Please change “DataSheet” and “C3:J15” to match your Excel file.

3. Copying to a Temporary Workbook

Set tempWb = Workbooks.Add
exportArea.Copy tempWb.Worksheets(1).Range("A1")

To export only the specified range accurately, we first create a new empty workbook using Workbooks.Add. Then, we use exportArea.Copy to copy the data from the specified range into this new workbook.

4. Saving in Text Format

tempWb.SaveAs ThisWorkbook.Path & "\ExportedData.txt", FileFormat:=xlText

This line is the heart of the file saving process. The SaveAs method saves the temporary workbook containing the copied data.

• ThisWorkbook.Path & "\ExportedData.txt": This gets the folder path where the macro-enabled Excel file is located and saves the file there with the name “ExportedData.txt”. You can change the filename.
• FileFormat:=xlText: This is the most important argument. By specifying xlText, the file is saved as a text file where cell values are separated by tabs (Tab-Delimited).

5. Post-processing

tempWb.Close SaveChanges:=False
MsgBox "Export to text file is complete."

Once the text file is created, the temporary workbook (tempWb) is no longer needed. We close it using the Close method. Setting SaveChanges:=False ensures it closes quietly without asking to save changes. Finally, a completion message is displayed.

Customization Points

To use this macro in your own work, you mainly need to edit the “Configuration Area” at the top of the code.

• Sheet Name: Change "DataSheet" in ThisWorkbook.Worksheets("DataSheet") to your actual sheet name.
• Cell Range: Change "C3:J15" in .Range("C3:J15") to the address of the range you want to export.
• File Name: Change "ExportedData.txt" in "\ExportedData.txt" to your preferred output filename.

Summary

In this article, I introduced how to export a specific Excel range as a tab-delimited text file using VBA. Once you set up this macro, you can complete routine file export tasks with a single button click. This eliminates manual errors and significantly reduces work time. Please give it a try!