How to use VBA to write data to a Text file?

Do you need to write an Excel data table to the text file using excel VBA? This article will show you step by step how to use VBA to write data to a text file from your workbooks.

You may be asked to export your excel data table to a text file with a delimiter such as (Comma, Semicolon, Tab, Special characters, etc)

Suppose you have the below data Sales Table in your excel file and need to export it to a text file.

Country Sales Table in Excel File: 3 Fields [ Country, Sales, Profit ] and 10 Records (incl. header)

VBA script will create a text file salesTable.txt and your final output looks like the below image.

VBA Script

Sub ExportData()
'Delcare Variables
Dim fileName As String, rg As Range, cVal As Variant, rowNo As Integer, colNo As Integer

'Path for your text file
fileName = "D:\Projects\xl\pptExcel\WriteTextFile\salesTable.txt"

'Data table need to export on text file
Set rg = ActiveSheet.Range("A1:C11")

Open fileName For Output As #1

'Loop to iterate both rows and columns
'No of Rows
For rowNo = 1 To rg.Rows.Count

    'No of Columns
    For colNo = 1 To rg.Columns.Count
        cVal = rg.Cells(rowNo, colNo).Value
        
        'current value on text file
        If colNo = rg.Columns.Count Then
            Write #1, cVal 'write only text for last record
        Else
            Write #1, cVal, 'write text with comma
        End If
        
    Next colNo
Next rowNo
        
'Close your data file
Close #1

End Sub

Copy the above VBA Script and Paste it into your VBA module

If you need to use any other delimiters instead of commas, you can update the below line of code.

Write #1, cVal, 'write text with comma

Please follow the below steps to assign the above macro to your worksheet button

1. Add a shape Export Data to your worksheet.

2. Right-click on the Export Data button and choose Assign Macro…
3. Select ExportData Macro from the list of macros in the Assign Macro dialog box
4. Save your excel file as Excel Macro-Enabled Workbook  *.xlsm
5. Click the button Export Data 

Conclusion

Reading and writing a text file is a basic operation for data analysis. In excel you can simply open your excel file copy the data table and paste it into your text file manually. You can use VBA Script If you need to automate the process to write data from your excel file.