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.
'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)
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.
Related – VBA Option Explicit
'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)
'close command to close the text file after adding data