How to Export or Save Each Row as Text File in Excel

Excel is a powerful spreadsheet program developed by Microsoft. It is widely used for organizing, analysing, and manipulating data in various industries and professions. To export multiple columns into individual text files in Excel, you can use VBA (Visual Basic for Applications) macros.

Steps to Export or Save Each Row as Text File in Excel

Here's an example of how you can do it:

Step 1 :Open the Excel file in which you want to export the rows as text files. Press Alt + F11 to open the Visual Basic Editor in Excel.

Insert a new module by clicking on "Insert" and selecting "Module." In the module window, paste the following code:

Example

Sub ExportRowsAsTextFiles()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long
    Dim rowRange As Range
    Dim cellValue As String
    Dim filePath As String
    Dim cell As Range
    
    Set ws = ThisWorkbook.ActiveSheet ' Change to the appropriate sheet if needed
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Assumes data starts in column A
    
    ' Loop through each row
    For rowNum = 1 To lastRow
        ' Set the range for the current row
        Set rowRange = ws.Range("A" & rowNum & ":" & ws.Cells(rowNum, ws.Columns.Count).End(xlToLeft).Address)
        
        ' Initialize cellValue as an empty string
        cellValue = ""
        
        ' Loop through each cell in the row
        For Each cell In rowRange
            ' Check the data type of the cell value
            Select Case True
                Case IsNumeric(cell.Value) ' Numeric value
                    cellValue = cellValue & CStr(cell.Value) & ","
                Case IsDate(cell.Value) ' Date value
                    cellValue = cellValue & Format(cell.Value, "dd-mm-yyyy") & ","
                Case Else ' Text value or other types
                    cellValue = cellValue & CStr(cell.Value) & ","
            End Select
        Next cell
        
        ' Remove the trailing comma
        cellValue = Left(cellValue, Len(cellValue) - 1)
        
        ' Define the file path for the text file (change as needed)
        filePath = "E:\Assignments\3rd Assignments\How to export or save each row as text file in Excel\Output\file_" & rowNum & ".txt"
        
        ' Export the row as a text file
        Open filePath For Output As #1
        Print #1, cellValue
        Close #1
    Next rowNum
    MsgBox "Rows exported to individual text files."
End Sub

Step 2 : Modify the code if needed:

• Set the "ws" variable to the worksheet you want to export the rows from. By default, it exports from the active sheet.

• Adjust the column reference in ws.Range("A" & rowNum & ":" & ws.Cells(rowNum, ws.Columns.Count).End(xlToLeft).Address) to match the column range you want to export.

• Update the filePath variable to the desired path where you want to save the text files. The example code saves them with a file name of file_1.txt, file_2.txt, etc., in my folder. Make sure to change the path to a valid directory.

Step 3 : Run the macro by pressing Alt + F8, selecting " ExportRowsAsTextFiles," and clicking "Run."

The code will iterate through each row in the specified worksheet, concatenate the values in that row into a comma?separated string, and save it as a text file with the specified file path. Each row will have its own text file.

Conclusion

To export each row in an Excel file as a separate text file, you can utilize VBA (Visual Basic for Applications) code. The code iterates through each row of the specified worksheet, concatenating the values in that row into a comma?separated string. The string is then saved as a text file with a unique file path for each row.

The code includes error handling to handle different data types within the row. It checks if a value is numeric, a date, or falls into the "else" category, which covers text and other types. The appropriate conversion is applied, and the values are appended to the cellValue variable. The resulting string is saved as a text file using the specified file path. This process ensures that each row in the Excel file is exported as an individual text file, allowing for further analysis or manipulation outside of Excel.