Automate the Export of Data from Excel to a text file with VBA

Objective

The goal here is to automate the process of exporting data from an Excel worksheet into a text file, where each row in the text file corresponds to a row of data in Excel. The values of each cell will be separated by a tab (or another delimiter, such as a comma, if needed).

Steps

1. Open the VBA editor: In Excel, press Alt + F11 to open the VBA editor.
2. Create a new module: In the VBA editor, click on Insert then Module to create a new module where you will paste the code.
3. Write the VBA code: Paste the code provided below into the module.

VBA Code to Export Data to a Text File

Sub ExportToTextFile()
    ' Declare variables
    Dim ws As Worksheet
    Dim row As Long, col As Long
    Dim filePath As String
    Dim textFile As Integer
    Dim separator As String   
    ' Set the worksheet to export
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with the name of your worksheet   
    ' Specify the file path and name for the text file
    filePath = "C:\Path\To\YourFile.txt" ' Replace with your desired file path   
    ' Set the separator (here tab, you can use other separators like "," for CSV)
    separator = vbTab   
    ' Open the text file for output
    textFile = FreeFile
    Open filePath For Output As textFile   
    ' Loop through each row of the worksheet
    For row = 1 To ws.UsedRange.Rows.Count
        ' Create a string for each row
        Dim rowText As String
        rowText = ""       
        ' Loop through each column of the row
        For col = 1 To ws.UsedRange.Columns.Count
            ' Append the cell value to the row string with a separator
            rowText = rowText & ws.Cells(row, col).Value & separator
        Next col       
        ' Remove the trailing separator
        rowText = Left(rowText, Len(rowText) - 1)       
        ' Write the row to the text file
        Print #textFile, rowText
    Next row   
    ' Close the text file
    Close textFile
    ' Confirmation message
    MsgBox "Export completed successfully!", vbInformation
End Sub

Explanation of the Code:

1. Variable Declarations:
   • ws: Refers to the worksheet from which you want to export data.
   • row and col: Used to loop through the rows and columns of the worksheet.
   • filePath: The file path and name for the text file where the data will be exported.
   • textFile: A variable to handle the file for writing.
   • separator: The character used to separate the values in the text file (e.g., tab with vbTab or a comma for CSV).
2. Setting the Worksheet:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 ») sets the worksheet to export. Replace « Sheet1 » with the actual name of the sheet you want to export.
3. Setting the File Path:
   • filePath = « C:\Path\To\YourFile.txt » sets the path of the text file to save the data. Modify this with your desired path and filename.
4. Opening the File for Output:
   • Open filePath For Output As textFile opens the file in write mode. If the file does not exist, it will be created.
5. Looping Through the Data:
   • The For row = 1 To ws.UsedRange.Rows.Count loop goes through each row in the used range of the worksheet.
   • The For col = 1 To ws.UsedRange.Columns.Count loop goes through each column of the current row.
6. Building the Row Text:
   • For each cell in a row, the cell’s value is appended to rowText followed by a separator (tab or comma). After the loop, the last separator is removed with Left(rowText, Len(rowText) – 1) to avoid having an extra separator at the end of the line.
7. Writing the Row to the Text File:
   • Print #textFile, rowText writes the row of data to the text file.
8. Closing the File:
   • Close textFile closes the text file once all rows are written.
9. Confirmation Message:
   • After the export is completed, a message box will pop up to confirm the success of the operation.

Customization Points:

• Separator: The separator is set by separator. For a CSV file, you can change separator = vbTab to separator = « , ».
• Worksheet Name: Modify Set ws = ThisWorkbook.Sheets(« Sheet1 ») to the name of the worksheet you wish to export.
• File Path: Modify the file path filePath = « C:\Path\To\YourFile.txt » to the desired location and filename for your text file.

Conclusion

This VBA script provides an efficient way to export Excel data to a text file with rows and columns properly formatted. It can be easily customized for different data structures and needs, such as exporting to a CSV or using a different delimiter.