How to Export Excel Data to Text Files in Excel

This post will explain how to export Excel data to text files in Excel using VBA code. This post will show you how to export one sheet to text files in Excel, which can be useful if you need to extract data from Excel and use it in another program that requires a text file format.

You can easily select the data you want to export, specify a file name and location for the text file, and then generate a text file that contains the data in the format you need.

Export one Single Worksheet to Text File

If you want to export a single Excel sheet to a text file, follow these steps:

Step1: Open the Excel workbook that contains the sheet you want to export.

Step2: Click on the sheet you want to export.

Step3: Click on the “File” tab in the ribbon menu.

Step4: Click on “Save As” in the left-hand menu.

Step5: In the “Save As” dialog box, choose the location where you want to save the text file.

Step6: In the “Save as type” dropdown menu, select “Text (Tab delimited) (*.txt)“.

Step7: Click on the “Save” button.

Export one Column or Selection to Text File with VBA Code

You can use VBA code to export a selection or one column to a text file in Excel. Here are the steps:

Step1: Press “Alt + F11” on your keyboard. This will open the Visual Basic Editor window.

Step2: In the Visual Basic Editor window, select “Insert” from the top menu and choose “Module” to create a new module.

Step3: Copy and paste the following VBA code into the new module. Save the workbook with the VBA code.

Sub ExportSelectionToTextFile_ExcelHow()
    Dim myFile As String
    Dim rng As Range
    
    'Get the range to be exported from the user
    On Error Resume Next
    Set rng = Application.InputBox("Please select the range to be exported:", Type:=8)
    On Error GoTo 0
    
    'If no range is selected, exit the sub
    If rng Is Nothing Then Exit Sub
    
    'Create a new workbook and copy the selected range to it
    Dim wb As Workbook
    Set wb = Workbooks.Add
    rng.Copy Destination:=wb.Sheets(1).Range("A1")
    
    'Get the file name and location for the text file
    myFile = Application.GetSaveAsFilename(InitialFileName:="ExportedData.txt", FileFilter:="Text Files (*.txt), *.txt")
    
    'If the user cancels the file selection, exit the sub
    If myFile = "False" Then Exit Sub
    
    'If the file name is not valid, display an error message and exit the sub
    If Len(myFile) = 0 Then
        MsgBox "Invalid file name. Please select a valid file name and location.", vbExclamation, "Export to Text File"
        Exit Sub
    End If
    
    'Save the new workbook as a text file and close it
    wb.SaveAs FileName:=myFile, FileFormat:=xlText, CreateBackup:=False
    wb.Close SaveChanges:=False
    
    'Display a success message
    MsgBox "Data has been exported to text file successfully!", vbInformation, "Export to Text File"
End Sub

Step4: Press “Alt + F8” to open the Macro dialog box. Select the ” ExportSelectionToTextFile_ExcelHow” macro from the list of macros and click “Run“.

Step5: select the range to be exported

Step6: select a file name and location for the text file to be exported.

Step6: Click “Save” to export the selected range to the text file.

The selected range should now be exported to a text file in the location that you specified.

Video: Export Excel Data to Text Files This

This video will demonstrate how to export Excel data to text files in Excel using VBA Code.