Write Data to Text Files Using VBA

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 the rows. 

Sales Data in Excel: 5 columns and 25 rows

VBA code to create a text file as below

VBA Code

• Declaring Variables :  

‘Variable declarations

Dim myFileName As String, rng As Range, CellVal As Variant, row As Interger, col As Interger

• 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 filemyFileName = “D:\Excel\WriteText\sales.txt”‘Data range need to write on text fileSet rng = ActiveSheet.Range(“A1:E26”)

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

‘Open text fileOpen 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 RowsFor 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 colNext row

Close the file

Close #1

Approach

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

Step 2: Right-click on ????reate a Text file” and ????ssign Macro..”

Step 3: Select MacroToCreateTextFile

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

Step 5: Click “Create Text file”

This VBA Program reads an Excel Range (Sales Data) and writes to a Text file (Sales.txt). This helps in faster copying of data to a text file as VBA coding enables automated copying. Let us learn how to do it in a few easy steps: