How to Write Data to Text File in Excel VBA?
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
VBA code to create a text file as below
- Declaring Variables :
|myFileName||String||Output text file (Full path with file name)|
|rng||Range||Excel range to read|
|cellVal||Variant||Variable to assign each cell value|
'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
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”