How to create and write to a txt file using VBA

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:

Excel VBA Write Text File

In the realm of Excel VBA, the power to interact with text files unfolds. Text file operations encompass opening, reading, and writing. When we talk about writing a text file, we essentially aim to transform the data residing in an Excel sheet into a text file or a notepad file. This process manifests through two distinct methods: Leveraging the FileSystemObject property within VBA or embracing the Open and Write approach embedded with the VBA framework.

Syntax of Open Text File

Open [File_path], For [Mode], As [File_Number]

File Path: The Path of the file we are trying to open on the computer.

Mode: The Mode is the control we can have over opening text files. We can have three types of control over the text file:

• Input Mode: The term 'Imput Mode' signifies a read-only control over the opened text file. When utilizing this mode, your interaction is restricted solely to reading the file's contents. It's akin to a one-way street, allowing you to extract data from the file without altering or modifying its existing content. This mode is ideal for scenarios where you seek information without any intention of modifying the original data.
• Output Mode: In the realm of "Output Mode", the canvas is yours to paint upon. This mode empowers you to write and create new content within the field. However, a word of cautions is essential: exercising this mode entails overwriting any pre-existing data. This serves as a powerful tool for crafting new narritives within the file, but it demands a vigilant approach to avoid unintended loss of valuable data.
• Append Mode: Contrary to the overwrite nature of the Output Mode,"Append Mode" offers a harmonious coexistence between new and existing data. This mode invites you to add fresh content at the end of the file's current data. Imagine it as seamlessly extending the storyline of an already captivating narrative. The Append Mode's gentle touch ensures that your new data complements the old, offering a balance fusion of information.

File Number: This will count the text file number of all the opened text files. It will recognize the opened file numbers in integer values from 1 to 511. However, assigning the file number is tricky and leads to confusion.

Free File: It returns the unique number for the opened files. This way, we can assign the unique file number without duplicate values.

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”