How to Write to a Text File Using Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can write to a text file using VBA in Excel.


How to Write to a Text File Using Excel VBA (Quick View)

Sub Write_to_Text_File()

File_Name = "TextFile.txt"
File_Location = "E:\ExcelDemy"
Text = "This is a test sentence."

Set File_System = CreateObject("Scripting.FileSystemObject")
Set File_Create = File_System.CreateTextFile(File_Location + "\" + File_Name)

File_Create.Write Text

File_Create.Close

End Sub

VBA Code to Write to a Text File Using Excel VBA


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


An Overview of the VBA Code to Write to a Text File Using Excel VBA (Step-by-Step Analysis)

So, let’s go to our main discussion today without further delay. We’ll break down the VBA code step-by-step to learn how to write to a text file using Excel VBA.

⧪ Step 1: Inserting the Necessary Inputs

First of all, we have to insert the necessary inputs into the code. The inputs include the file name, the file location, and the text that you want to write.

Put these according to your needs.

File_Name = "TextFile.txt"
File_Location = "E:\ExcelDemy"
Text = "This is a test sentence."

Inserting Inputs to Write to a Text File Using Excel VBA

⧪ Step 2: Declaring the Necessary Objects

Next, we need to declare a few necessary objects to the code. They are: a FileSystemObject and a FileCreateObject. The FileCreateObject will create the required text file in our given location.

Set File_System = CreateObject("Scripting.FileSystemObject")
Set File_Create = File_System.CreateTextFile(File_Location + "\" + File_Name)

Setting Objects to Write to a Text File Using Excel VBA

⧪ Step 3: Writing the Text to the File

This is the most important step. We’ll write the required text to the text file using the Write method of VBA.

File_Create.Write Text

⧪ Step 4: Closing the FileCreateObject

Finally, we’ll close the FileCreateObject.

File_Create.Close

Closing Object to Write to a Text File Using Excel VBA

So the complete VBA code will be:

VBA Code:

Sub Write_to_Text_File()

File_Name = "TextFile.txt"
File_Location = "E:\ExcelDemy"
Text = "This is a test sentence."

Set File_System = CreateObject("Scripting.FileSystemObject")
Set File_Create = File_System.CreateTextFile(File_Location + "\" + File_Name)

File_Create.Write Text

File_Create.Close

End Sub

VBA Code to Write to a Text File Using Excel VBA

Read More: Macro to Convert Excel to Pipe Delimited Text File (3 Methods)


Similar Readings


Developing a Macro to Write a Text to a Text File Using Excel VBA

We’ve seen the step-by-step analysis of the code to write a text to a text file using VBA.

Now let’s see how to use the code to send the Email

⧪ Step 1: Opening the VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.

Inserting Module to Write to a Text File Using Excel VBA

⧪ Step 3: Putting the VBA Code

This is the most important step. Insert the given VBA code in the module.

Putting Code to Write to a Text File Using Excel VBA

⧪ Step 4: Running the Code

Click on the Run Macro tool from the toolbar above.

The code will run. And you’ll find a text file created in your desired location (E:\ExcelDemy) with the desired name (TextFile.txt).

It contains the given text.

Output to Write to a Text File Using Excel VBA

Read More: How to Apply Macro to Convert Multiple Excel Files to CSV Files


Things to Remember

  • Make sure the location mentioned in the code to create the text file is a valid one on your computer. Otherwise, it’ll show an error.
  • If you want to select the folder manually after running the code each time, other than fixing it within the code, you can use the VBA FileDialog object.

Conclusion

Therefore, this is the process to develop a Macro to send an Email from Excel with an attachment. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo