Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to Write Data to Text File in Excel VBA?

  • Last Updated : 29 Oct, 2021

This VBA Program reads an Excel Range (Sales Data) and write to a Text file (Sales.txt)

Excel VBA code to read data from an Excel file (Sales Data – Range “A1:E26”).  Need two “For loop” for rows and columns. Write each value with a comma in the text file till the end of columns (write without comma only the last column value). Do the above step until reach the end of rows. 

Sales Data in Excel: 5 columns and 25 rows

Sales Data

VBA code to create a text file as below

VBA Code:

  • Declaring Variables :  
VariableData TypeComments
myFileName StringOutput text file (Full path with file name)
rngRangeExcel range to read 
cellValVariantVariable to assign each cell value
rowIntegerIterate rows
colIntegerIterate columns
'Variable declarations
Dim myFileName As String, rng As Range, cellVal As Variant, row As Integer, col As Integer
  • Initialize variables:
    • myFileName: The file name with the full path of the output text file
    • rng: Excel range to read data from an excel.
'Full path of the text file
myFileName = "D:\Excel\WriteText\sales.txt"
'Data range need to write on text file
Set rng = ActiveSheet.Range("A1:E26")

Open the output text file and assign a variable “#1”

'Open text file
Open myFileName For Output As #1

‘Nested loop to iterate both rows and columns of a given range eg: “A1:E26” [5 columns and 26 rows]

'Number of Rows
For row = 1 To rng.Rows.Count
   'Number of Columns
   For col = 1 To rng.Columns.Count

Assign the value to variable cellVal 

cellVal = rng.Cells(row, col).Value

Write cellVal with comma.  If the col is equal to the last column of a row.  write-only value without the comma.

'write cellVal on text file
    If col = rng.Columns.Count Then
        Write #1, cellVal 
    Else
        Write #1, cellVal, 
    End If

Close both for loops

   Next col
Next row

Close the file

Close #1

Approach:

Step 1: Add a shape (Create Text File) to your worksheet 

Step 2: Right-click on “Create a Text file” and “Assign Macro..”

Step 3: Select MacroToCreateTextFile

Step 4: Save your excel file as “Excel Macro-Enabled Workbook”  *.xlsm

Step 5: Click “Create Text file

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!