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.

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: