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:

  • First, you need to use a FileSystemObject or Folder object to use with the method.
    1-create-a-text-fie-using-vba
  • After that, you need to create another object use the CreateTextFile method.
    2-create-text-file-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.
    3-write-statement
  • 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.

  • First, you need to declare variables to use in the code.
    5-declare-variables
  • 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
  • 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
  • From here, you need to declare the “FreeFile” command to a variable to get the file number.
    8-freefile-command
  • Now, you need to create use the “Output” command as you need to add data to the file.
    9-output-command
  • 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
  • Also, you need to use the close command to close the text file once data has been added to it.
    11-close-command
  • 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