Create and Write to a Text File Using VBA

Sometimes we may want to transfer the data we have in our Excel worksheets to a text file for archiving or other purposes. We can create a text file and write to it using Excel VBA. This tutorial shows you how.

Preliminary information for creating a text file using Excel VBA

To create a text file using Excel VBA, we need to use the CreateTextFile method with the FileSystemObject.

Explanation of the CreateTextFile method

The CreateTextFile method creates a specified file name and returns a TextStream object that can be used to write to or read from the file. The TextStream object is normally replaced by a placeholder variable representing the TextStream object returned from the FileSystemObject.

The syntax of the CreateTextFile method is:

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

object	[required] – It is always the name of a FileSystemObject
filename	[required] – It is a string expression that identifies the file to be created.
overwrite	[optional] – It is a Boolean value that shows if an existing file with a similar name can be overwritten. The value is True if the file can be overwritten and False if the file cannot be overwritten. If it is omitted, the True value is assumed, and the file can be overwritten. A False value generates an error if a file with a similar name exists.
unicode	[optional] – It is a Boolean value that shows whether the file is created as a Unicode or ASCII file. The value is True if the file is created as an ASCII file and False if it is created as a Unicode file. If it is omitted an ASCII file is assumed.

Note: ASCII (American Standard Code for Information Interchange) is the standard that represents text in computers and is limited to a few English alphabet characters such as lowercase and uppercase letters, symbols, and digits (0-9). It is the most universal standard. Unicode (Universal Character Set) standard is designed to include characters appearing in most languages including Arabic, Greek, Japanese, Chinese, and so on.

Explanation of the FileSystemObject

We use the FileSystemObject or FSO to access the system of files on our computer. We can use the FSO to access four types of objects: Drive, Folder, File, and Text Stream.

The Drive, Folder, and File objects are used to access the mentioned drive, folder, and file respectively. The Text Stream object is used to read or write a text file.

The FSO is not present in Excel VBA by default. We use the following steps to enable it:

Open an Excel worksheet and press Alt + F11 to open the Visual Basic Editor (VBE).
In the VBE click Tools >> References.

Graphical user interface, application, Word

Description automatically generated

In the References dialog box that pops up check the Microsoft Scripting Runtime checkbox and click the OK button.

Graphical user interface, text, application

Description automatically generated

We are now able to access FSO in Excel VBA.

How to create a text file in Excel VBA

We use the followings steps to create a text file using Excel VBA:

Open an Excel worksheet and press Alt + F11 to open the Visual Basic Editor.
Click Insert >> Module to create a new module.

In the new module, type in the following procedure remembering to change the path to the text file to your path:

Sub createTextFile()
    Dim fSO As FileSystemObject
    Dim myTextFile As Object
    Set fSO = New FileSystemObject
    Set myTextFile = fSO.CreateTextFile("C:\Users\Sirali\OneDrive\Desktop\myFolder\myTextFile", True)
End Sub

Sub createTextFile()

Dim fSO As FileSystemObject

Dim myTextFile As Object

Set fSO = New FileSystemObject

Set myTextFile = fSO.CreateTextFile("C:\Users\Sirali\OneDrive\Desktop\myFolder\myTextFile", True)

End Sub

Save the procedure and save the workbook as a macro-enabled workbook.
Place the cursor anywhere inside the procedure and press F5 to run the code.
Open the location where you created the file to confirm that the text file was created.

Graphical user interface, application

Description automatically generated

How to write to the text file using Excel VBA

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

Print. With this statement, we can write data to a text file that retains the appearance of the data we have copied from Excel.
Write. With this statement, we can write data to a text file where we will have commas between values, # signs around dates, and quote marks around strings.

Write to the text file using the Print statement

Let’s write code to write the following dataset to the myTextFile we created previously using the Print statement:

We use the following steps:

From the active worksheet press Alt + F11 to open the VBE and insert a new module.
In the new module we need to first declare the variables to use in the code: a variable called TextFile of Integer type, a variable called iCol of Integer type, a variable called myRange of Range type, a variable called i of Integer type, and a variable called myTextFile of String type.

Next, we define the range that we want to copy to the text file. We also define the number of the cells of the range to a variable to be used as a counter for the loop that will be used to add data to the text file.

We then define the path to the text file to which we want to write the data. Ensure to adjust the path to your path.

We assign the FreeFile command to the TextFile variable. The FreeFile function returns the unique integer number to our text file and preserves the next available file number.

We then use the For Next loop to get values from the Excel range and add them to the text file one by one.

Finally, we use the Close command to close the text file once the process of adding data to it is complete. The Close method flushes the buffer and closes the file.

The complete procedure now is as displayed below:

Sub dataToTextFile()
    'The variables that we need to use in the code
    Dim TextFile As Integer
    Dim iCol As Integer
    Dim myRange As Range
    Dim i As Integer
    Dim myTextFile As String
    
    'Define the range that we want to write
    Set myRange = Range("A1:B11")
    iCol = myRange.Count
    
    'The path to the text file (Ensure to change this path to your path)
    myTextFile = "C:\Users\Sirali\OneDrive\Desktop\myFolder\myTextFile"
    
    'Define FreeFile to the variable file number
    TextFile = FreeFile
    
    'Use the Output command to add text to the end of the file
    Open myTextFile For Output As TextFile
    
    'Define the 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
    
    'The close command to close the text file after adding data
    Close #TextFile
End Sub

Sub dataToTextFile()

'The variables that we need to use in the code

Dim TextFile As Integer

Dim iCol As Integer

Dim myRange As Range

Dim i As Integer

Dim myTextFile As String

'Define the range that we want to write

Set myRange = Range("A1:B11")

iCol = myRange.Count

'The path to the text file (Ensure to change this path to your path)

myTextFile = "C:\Users\Sirali\OneDrive\Desktop\myFolder\myTextFile"

'Define FreeFile to the variable file number

TextFile = FreeFile

'Use the Output command to add text to the end of the file

Open myTextFile For Output As TextFile

'Define the 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

'The close command to close the text file after adding data

Close #TextFile

End Sub

Save the procedure and save the workbook as a macro-enabled workbook.
Place the cursor anywhere in the procedure and press F5 to run the code.
Open the myTextFile to see the results:

Table

Description automatically generated

Write to the text file using the Write statement

Let’s write code to write the following dataset to the myTextFile file we created previously using the Write statement:

We use the following steps:

From the active worksheet that contains the dataset press Alt + F11 to open the Visual Basic Editor and insert a new module.
In the new module we first need to declare the variables to be used in the code: a variable called myTextFile of type String, an object variable called rng of Range type, a variable called cellValue of Variant type, a variable called i of Integer type, and a variable called j of Integer type. We use the Variant type for the cellValue variable because it can hold any type of data.

We then specify the full path to the myTextFile text file. Remember to change this path to your path on your device:

We then initialize the range object rng with the dataset range.

Next, we add a statement that allows the text file to be written to. We can then refer to the text file as #1 in the rest of the code. The Output command is used when we want to write data to or modify data in a test file.

We start a double Loop.

Note: the statement rng.Rows.Count returns the number of rows in the dataset, which is 12 in this case. The statement rng.Columns.Count returns the number of columns in the dataset, which is 2 in this case.

Excel VBA writes the value of a cell to the cellValue variable.

We add the following lines of code to write the values passed to the cellValue variable to the text file.

Note: Because of the If Then Else statement, Excel VBA only starts a new line (Write #1, cellValue) when j equals the number of columns, meaning the last value in a row has been reached. To delimit the values with a comma we use Write #1, cellValue, (with a comma).

We close both loops.

We close the text file once the process of writing data to it is complete. The Close method flushes the buffer and closes the file.

The final procedure is as displayed below:

Sub dataToTextFile2()
    Dim myTextFile As String
    Dim rng As Range
    Dim cellValue As Variant
    Dim i As Integer
    Dim j As Integer
    myTextFile = "C:\Users\Sirali\OneDrive\Desktop\myFolder\myTextFile"
    Set rng = Range("A1:B11")
    Open myTextFile For Output As #1
    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
        cellValue = rng.Cells(i, j).Value
    If j = rng.Columns.Count Then
        Write #1, cellValue
    Else
        Write #1, cellValue,
    End If
        Next j
    Next i
    Close #1
    
End Sub

Sub dataToTextFile2()

Dim myTextFile As String

Dim rng As Range

Dim cellValue As Variant

Dim i As Integer

Dim j As Integer

myTextFile = "C:\Users\Sirali\OneDrive\Desktop\myFolder\myTextFile"

Set rng = Range("A1:B11")

Open myTextFile For Output As #1

For i = 1 To rng.Rows.Count

For j = 1 To rng.Columns.Count

cellValue = rng.Cells(i, j).Value

If j = rng.Columns.Count Then

Write #1, cellValue

Else

Write #1, cellValue,

End If

Next j

Next i

Close #1

End Sub

Save the procedure and save the workbook as a macro-enabled workbook.
Place the cursor anywhere in the procedure and press F5 to run the code.
Open the myTextFile file to see the results.

Conclusion

Sometimes we may want to transfer our Excel dataset to a text file for archiving purposes or some other purpose. In this tutorial, we have explained in detail how to create and write to a text file using Excel VBA.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 1,424