VBA Create and Write to a Text File

To create a text using a VBA code, you need to use the CreateTextFile method. This method allows you to define a location where you want to create it. This method has a syntax that where you can specify if you want to overwrite the file on the location and specify whether the file is created as a Unicode or ASCII file.

Create a Text File using VBA

Use the following steps:

1. First, you need to use a FileSystemObject or Folder object to use with the method.
   
2. After that, you need to create another object use the CreateTextFile method.
   
3. In this code, we have used the TRUE to overwrite if there’s already a file with the same name in the folder.
4. In the end, when you run this macro, create a new text file in the folder, just like the following.

Sub create_text_file()

'object to use as folder
Dim fld As Object
Set fld = CreateObject("Scripting.FileSystemObject")

'using create text file method
Dim myFile As Object
Set myFile = fld.CreateTextFile("C:UsersDellDesktopmyFoldermyTextFile.txt", True)
 
End Sub

Syntax for CreateTextFile Method

CreateTextFile (filename, [ overwrite, [ unicode ]])

• filename: Path and the name of the file that you want to create.
• overwrite: Boolean to define if you want to overwrite the file (if already exists) (optional).
• unicode: Boolean to define whether the file is created as a Unicode or ASCII file (optional).

Write to a Text File using VBA

There are two statements that you can use to write data to a text file:

1. Write: With this statement, you can write data to a text file where you will have commas between values, quotes around strings, and # signs around dates.
   
2. Print: With this statement, you can write data to a text file with the exact appearance that you in the Excel worksheet.
   

Now ahead we will look at examples for both statements and understand how to write a complete code to write to a text file. But before that, you need to understand some of the terminologies to write the code the way you want.

• For Output: You can use this command when you want to write data or want to modify data into a text file.
• For Input: With this command, you can extract data from a text, but you won’t be able to modify and add data to the file.
• For Append: This command helps you to add new data to the bottom of the text file.
• FreeFile: You can use it to define a file number that is not in use to the text file that you want to use so that you would be able to refer to it.

Now let’s write a code to enter data into a text file.

1. First, you need to declare variables to use in the code.
   
2. After that, you need to define the range that you need to write to the text file. And use the count of the cells of the range as a counter for the loop by defining it to a variable.
   
3. Next, you need to define the address of the text file where you want to add data.
   
4. From here, you need to declare the “FreeFile” command to a variable to get the file number.
   
5. Now, you need to create use the “Output” command as you need to add data to the file.
   
6. Finally, you need to use the “For Next” loop to get values from range one by one and add it to the file.
   
7. Also, you need to use the close command to close the text file once data has been added to it.
   
8. In the end, when you run this macro, it adds data from the range A1:A13 to the text file that you have saved at the path you have mentioned.
   

Note: Make sure to change the path of the text file from the code according to the path you have in your system.

Related – VBA Option Explicit

Option Explicit
Sub data_to_text_file()

'variables that you need to use in the code
Dim TextFile As Integer
Dim iCol As Integer
Dim myRange As Range
Dim cVal As Range
Dim i As Integer
Dim myFile As String

'define the range that you want to write
Set myRange = Range("A1:A13")
iCol = myRange.Count

'path to the text file (MAKE SURE TO CHANGE IT)
myFile = "C:UsersDellDesktopNewFoldertextfile.txt"

'define FreeFile to the variable file number
TextFile = FreeFile

'using append command to add text to the end of the file
Open myFile For Output As TextFile

'loop to add data to the text file
For i = 1 To iCol
Print #TextFile, Cells(i, 1),
Print #TextFile, Cells(i, 2)
Next i

'close command to close the text file after adding data
Close #TextFile

End Sub

Relared