VBA Create and Write to a Text File

HomeVBA TutorialVBA 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.
    1-create-a-text-fie-using-vba
  2. After that, you need to create another object use the CreateTextFile method.
    2-create-text-file-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:\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:

  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.
    3-write-statement
  2. Print: With this statement, you can write data to a text file with the exact appearance that you in the Excel worksheet.
    4-print-statement

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.
    5-declare-variables
  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.
    6-define-the-range
  3. Next, you need to define the address of the text file where you want to add data.
    7-define-the-address-of-the-text-file
  4. From here, you need to declare the “FreeFile” command to a variable to get the file number.
    8-freefile-command
  5. Now, you need to create use the “Output” command as you need to add data to the file.
    9-output-command
  6. Finally, you need to use the “For Next” loop to get values from range one by one and add it to the file.
    10-for-next-loop-to-get-values
  7. Also, you need to use the close command to close the text file once data has been added to it.
    11-close-command
  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.
    12-run-the-macro

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.