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:

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.

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”

How can we open a text file for writing in Excel VBA?

To open a text file for writing in Excel VBA, you can use the ‘Open’ statement. Here’s an example:

Dim FileNum As Integer

FileNum = FreeFile

Open “C:\Path\To\Your\File.txt” for output As #FileNum

How do I write data to a text file in Excel VBA?

You can use the ‘Print’ statement to write data to a text file. Below is an example:

Print #FileNum, “This is a line of Text.”

How to write multiple lines of data to a text file?

You can use multiple ‘Print’ statements to write multiple lines of data, For Example:

Print #FileNum, “Line 1”

Print #FileNum, “Line 2”

Print #FileNum, “Line 3”

How to handle errors when writing to a text file?

You can use error handling technique like ‘On Error Resume Next’ Or ‘On Error Goto’ to handle errors that might occur while writing to a text file. Be sure to handle error gratefully to prevent unexpected behavior.