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

Table of Contents hide

Download Practice Workbook

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

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

Things to Remember

Conclusion

Download Practice Workbook

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

Write to Text File.xlsm

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."

⧪ 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)

⧪ 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

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

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.

⧪ Step 3: Putting the VBA Code

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

⧪ 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.

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.