In this article I will explain how you can create a text file and how write data to a text file in overwrite and append modes. 

You can download the file and code related to this article here.

Create a text file using the file system object:

You can create a text file using the file system object using the code below.

Sub CreateTextFile()
    Dim fs As Object
    Dim stream As Object

    Set fs = CreateObject("Scripting.FileSystemObject")
    On Error GoTo fileexists
        Set stream = fs.CreateTextFile("e:\TextFile.txt", False, True)

fileexists:
        If Err.Number = 58 Then
            MsgBox "File already Exists"
		 'Your code here
        Else
            stream.Write ("No new line character inserted")
            stream.WriteLine ("This will take the cursor to next line.")
            stream.Close
        End If
    On Error GoTo 0
End Sub

 

The CreateTextFile  function takes 3 arguments.

1. First is the name of the file along with the complete path
2. Overwrite – Second (optional) argument indicates if an existing file can be overwritten. The value is True if the file can be overwritten; False if it can’t be overwritten. Default is false
3. Unicode – Third (optional) argument indicates whether the file is created as a Unicode or ASCII file. The value is True if the file is created as a Unicode file; False if it’s created as an ASCII file. Default is ASCII.

As we have specified overwritten as false, an error will be thrown if the file already exists (Error number 58). So, we are using error handling to check for this condition.

We can use the Write and WriteLine function to write to the file. As you can guess, the WriteLine function will take the cursor to the next line, while the Write function will not. –

---

Example 1, Write One Line of Data to Text File:

The example below creates a text file at the location “D:Temp” under the name “Test.txt” and writes the value in cell A1 in the first line of the text file:

Sub Example1()
Open "D:TempTest.txt" For Output As #1
Write #1, Cells(1, 1)
Close #1
End Sub


The first line checks if the file “D:TempTest.txt” exists. If it doesn’t it will create a file named “Test.txt” at the location ” D:Temp”. If it does exist it will overwrite the file.

Open "D:TempTest.txt" For Output As #1

In our example the file does not exist, therefore a new file is created:

It has 3 parts:

“D:TempTest.txt”: Specifies the full path of the file.

Output: Specifies that this file is for writing to. This is opposed to the value Input used in the article Excel VBA, Reading Text Files

#1: This could be any number between #1 and #511. Whatever number is chosen here should be used in the rest of the program to reference this file.

The next line prints the data in cell A1 to the first line of the text file:

Write #1, Cells(1, 1)

The content of cell A1 can be seen below:

The last line of code closes the file that has the reference #1:

Close #1

Had we chosen another number when opening the file, we would have had to choose the same number here when closing it. For example lets say we decide to user the number #4. We would have to user the #4 throughout the code:

Sub Example1()
Open "D:TempTest.txt" For Output As #4
Write #4, Cells(1, 1);
Close #4
End Sub
End Sub


The code above will yield the same result as our original code.

Always remember to close the file at the end of your code. Closing the file does the following:

• Saves the data written to the file.
• Allows other programs to modify the file.
• If the file is not closed, you will not be able to reopen it using another number index.

Final Result:

–

---

Example 2, Write Multiple Lines of Data to Text File:

The example below creates a text file at the location “D:Temp” under the name “Test.txt” and writes all the data in column A to it:

Sub Example2()
Dim flag As Boolean
Dim i As Integer
'open the file for writing
Open "D:TempTest.txt" For Output As #4
flag = True
i = 1
'keeps going until the end of the file is reacheed
While flag = True
    'check if the current cell has data in it
    If Cells(i, 1) <> "" Then
        'write the data to the file
        Write #4, Cells(i, 1)
        'go to next cell
        i = i + 1
    Else
        'if the last row has been reached exit the loop
        flag = False
    End If
Wend
'close the file
Close #4
End Sub


The following data was written in column A:

Result:

–

---

Example 3, Save File Dialog:

In the example below the user will initially be asked to choose the location for the output text file using a save file dialog. A text file will then be created and the data in column A will be printed to the text file. For more information about save file dialogs please see the following links:

• VBA Save File Dialog, FileDialog(msoFileDialogSaveAs)
• Excel VBA, Save File Dialog, GetSaveAsFilename

Sub Example3()
Dim flag As Boolean
Dim i As Integer
Dim strPath As String
strPath = Application.GetSaveAsFilename(FileFilter:= _
"Text Files (*.txt), *.txt", Title:="Save Location")
If strPath <> "False" Then
    'open the file for writing
    Open strPath For Output As #4
   
    flag = True
    i = 1
   
    'keeps going until the end of the file is reacheed
    While flag = True
        'check if the current cell has data in it
        If Cells(i, 1) <> "" Then
        'write the data to the file
           Write #4, Cells(i, 1)
           'go to next cell
           i = i + 1
        Else
           'if the last row has been reached exit the loop
            flag = False
        End If
    Wend
    'close the file
    Close #4
End If
End Sub


The highlighted code creates a save file dialog and asks the user to select a location to save the file:

The final result is similar to the previous examples.

Sub Example4()
Dim flag As Boolean
Dim i As Integer
Dim strPath As String
On Error GoTo lblError:
strPath = Application.GetSaveAsFilename(FileFilter:= _
"Text Files (*.txt), *.txt", Title:="Save Location")
If strPath <> "False" Then
    'open the file for writing
    Open strPath     For Output As #4
    flag = True
    i = 1
    'keeps going until the end of the file is reached
    While flag = True
        'check if the current cell has data in it
        If Cells(i, 1) <> "" Then
           'write the data to the file
           Write #4, Cells(i, 1)
           'go to next cell
           i = i + 1
        Else
           'if the last row has been reached exit the loop
           flag = False
        End If
    Wend
    'close the file
    Close #4
End If
Exit Sub
lblError:
Err.Clear

End Sub


Example 5, Use print function in append mode:

The examples covered so far rewrite the existing file. Now let us look at how to open a file in append mode. Here we will be using the print function to write to the file. The difference being, the text will not be inserted within double quotes.

Sub AppendFile()
    Dim strFile_Path As String
    Dim rangeToWrite As range

    strFile_Path = "e:\TextFile.txt"

On Error GoTo cleanup
    Open strFile_Path For Append As #1

    Set rangeToWrite = range("A1:A10")

    For Each cell In rangeToWrite
        Print #1, cell.Value
    Next cell

    Close #1 

Cleanup:
    Close #1 

End Sub 

“Open file for Append” will first check if the file exists. If the file does not exist, a new one will be created. Else, the file will be opened in append mode.

We use the Print function to write the text from the Excel file to the text file. We have selected a single to column from Excel. You can easily modify the range to include multiple columns.

This is how the Excel and the text files look after executing the code.

If you run the code again, the text file will look like this:

You can download the file and code related to this article here.

See also:

• Excel VBA, Reading Text Files
• VBA, Modify Existing Text File
• VBA Save File Dialog, FileDialog(msoFileDialogSaveAs)
• Excel VBA, Save File Dialog, GetSaveAsFilename
• VBA, Check if Text File is Open
• VBA Append Data to Text File

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com