Export Data to Text File with Excel VBA

Goal:

This VBA code will allow you to export data from an Excel worksheet to a plain text file (CSV or tab-delimited). We’ll use VBA (Visual Basic for Applications) to create a macro that will save the data into a text file. I will walk through the process in detail.

Steps in the Code:

1. Define the Workbook and Worksheet: We’ll work with the active workbook and the active sheet. This will allow you to export data from whichever worksheet is active at the time you run the macro.
2. Open a Text File for Writing: We’ll use the Open statement to create and open a text file where the data will be saved. The file will be opened in write mode (this will overwrite any existing content).
3. Loop Through Data: We’ll loop through the cells of the active sheet, writing the data from each cell to the text file. The values will be separated by a delimiter, like a comma (CSV format) or a tab (tab-delimited format).
4. Handle the Text File Closure: After all the data has been written to the text file, we will close the file using the Close statement to save the changes.

Explanation of the Code:

Sub ExportDataToTextFile()
    ' Declare necessary variables
    Dim ws As Worksheet ' The worksheet to export data from
    Dim filePath As String ' The path where the text file will be saved
    Dim cell As Range ' Variable to loop through cells
    Dim rowNum As Long ' To keep track of row number while writing to the text file
    Dim colNum As Long ' To keep track of column number while writing to the text file
    Dim textFile As Integer ' File handler for the text file
    ' Set the active worksheet
    Set ws = ActiveSheet
    ' Specify the path to save the text file (change this as needed)
    filePath = Application.GetSaveAsFilename( _
        InitialFileName:="ExportedData.txt", _
        FileFilter:="Text Files (*.txt), *.txt", _
        Title:="Save As")  
    ' Check if the user canceled the save file dialog
    If filePath = "False" Then Exit Sub ' User canceled, so exit the procedure
    ' Open the text file for writing (1 = For Writing)
    textFile = FreeFile ' Get a free file number
    Open filePath For Output As textFile ' Open the file
    ' Loop through each row in the worksheet (you can define a range here if needed)
    For rowNum = 1 To ws.UsedRange.Rows.Count ' Loop through each row in the used range of the worksheet
        Dim rowData As String ' A string variable to hold the current row's data       
        ' Loop through each column in the current row
        For colNum = 1 To ws.UsedRange.Columns.Count
            ' Get the cell value from the worksheet and add it to rowData
            rowData = rowData & ws.Cells(rowNum, colNum).Value
            ' If it's not the last column, add a comma as a delimiter
            If colNum < ws.UsedRange.Columns.Count Then
                rowData = rowData & "," ' For CSV format
            End If
        Next colNum       
        ' Write the rowData to the text file (add a newline character)
        Print #textFile, rowData
    Next rowNum   
    ' Close the text file after writing all data
    Close textFile   
    ' Inform the user that the export is complete
    MsgBox "Data has been successfully exported to " & filePath, vbInformation, "Export Complete"
End Sub

Step-by-Step Breakdown:

1. Define Variables:
2. Dim ws As Worksheet ‘ The worksheet to export data from
3. Dim filePath As String ‘ The path where the text file will be saved
4. Dim cell As Range ‘ Variable to loop through cells
5. Dim rowNum As Long ‘ To keep track of row number while writing to the text file
6. Dim colNum As Long ‘ To keep track of column number while writing to the text file
7. Dim textFile As Integer ‘ File handler for the text file
   • ws: This represents the worksheet you’re exporting data from (it’s the active sheet).
   • filePath: This stores the full path of the text file.
   • cell: Used to loop through cells in the worksheet.
   • rowNum and colNum: Track which row and column we’re working with while writing data to the text file.
   • textFile: A file handler that represents the open text file.
8. Set Active Worksheet and Get File Path:
9. Set ws = ActiveSheet
10. filePath = Application.GetSaveAsFilename( _
11.     InitialFileName:= »ExportedData.txt », _
12.     FileFilter:= »Text Files (*.txt), *.txt », _
13.     Title:= »Save As »)
    • ws is set to the active worksheet (ActiveSheet).
    • filePath asks the user to select a location and name for the text file to save the data. If the user cancels, the macro exits without doing anything.
14. Open the Text File:
15. textFile = FreeFile ‘ Get a free file number
16. Open filePath For Output As textFile ‘ Open the file
    • FreeFile: Retrieves a free file number so that we can safely open a file for writing.
    • Open filePath For Output As textFile: Opens the file for output (writing) based on the filePath.
17. Loop Through Data:
18. For rowNum = 1 To ws.UsedRange.Rows.Count ‘ Loop through each row in the used range of the worksheet
19.     Dim rowData As String ‘ A string variable to hold the current row’s data
20.     For colNum = 1 To ws.UsedRange.Columns.Count
21.         rowData = rowData & ws.Cells(rowNum, colNum).Value
22.         If colNum < ws.UsedRange.Columns.Count Then
23.             rowData = rowData & « , » ‘ For CSV format
24.         End If
25.     Next colNum
26.     Print #textFile, rowData
27. Next rowNum
    • UsedRange is used to ensure we only loop through the rows and columns that actually contain data.
    • The outer loop (For rowNum) iterates over each row in the worksheet.
    • The inner loop (For colNum) iterates over each column in the current row.
    • The cell value is added to the rowData string, and a comma is added as a delimiter between columns (for CSV format). For other delimiters, change the « , » to something else, such as vbTab for tab-delimited files.
    • Print #textFile, rowData writes the constructed rowData to the text file.
28. Close the File:
29. Close textFile
    • This closes the text file, ensuring that all data is written and saved properly.
30. Confirmation Message:
31. MsgBox « Data has been successfully exported to  » & filePath, vbInformation, « Export Complete »
    • A message box pops up, informing the user that the export has been successful.

Customization:

• Delimiter: If you want a different delimiter (e.g., tab-delimited), you can replace « , » with vbTab for tabs or any other character.
• Range to Export: Instead of using ws.UsedRange, you can define a specific range, such as ws.Range(« A1:D10 »), if you want to export a specific set of cells.