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:
- First, you need to use a FileSystemObject or Folder object to use with the method.
- After that, you need to create another object use the CreateTextFile method.
- In this code, we have used the TRUE to overwrite if there’s already a file with the same name in the folder.
- 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:\Users\Dell\Desktop\myFolder\myTextFile.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:
- 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.
- 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.
- First, you need to declare variables to use in the code.
- 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.
- Next, you need to define the address of the text file where you want to add data.
- From here, you need to declare the “FreeFile” command to a variable to get the file number.
- Now, you need to create use the “Output” command as you need to add data to the file.
- Finally, you need to use the “For Next” loop to get values from range one by one and add it to the file.
- Also, you need to use the close command to close the text file once data has been added to it.
- 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.
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:\Users\Dell\Desktop\NewFolder\textfile.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
If you are preparing for an interview, make sure to check out these VBA interview questions.