In this article I will explain how you can append data to a text file. I am assuming readers are familiar with modifying text files. If you are not familiar with modifying text files I’d recommend taking a look at the this article first VBA, Modify Existing Text File.

Jump To:

• Example 1, Basics
• Example 2, Using Dialogs

You can download the file and code related to this article here. Please note that although the code is in an Excel workbook, it is applicable to all MS Office products.

Example 1, Basics:

Assume we have the following data in a text file:

The file is located in the “D:Temp with the name “Test.txt”. The code below will append the string “Data to be appended” to the end of the text file:

Sub Example1()
'the final string to print in the text file
Dim strData As String
'each line in the original text file
Dim strLine As String
strData = ""
'open the original text file to read the lines
Open "D:TempTest.txt" For Input As #1
'continue until the end of the file
While EOF(1) = False
    'read the current line of text
    Line Input #1, strLine
    'add the current line to strData
    strData = strData + strLine & vbCrLf
Wend
'add the new line
strData = strData + "Data to be appended"
Close #1
'reopen the file for output
Open "D:TempTest.txt" For Output As #1
Print #1, strData
Close #1
End Sub


Result:

The program does 3 things:

• Reads all the lines of the original text file and stores them in the variable strData. For more information about reading data from text files please see Excel VBA Reading Text Files.
• Concatenates the string “Data to be appended” to the string variable strData. For more information about concatenating string variables please see VBA Excel String Processing and Manipluation.
• Creates a new text file and overwrites the original text file with the data in the variable strData. For more information about writing data to text files please see Excel VBA Writing to a Text File.

The line below opens the original text file:

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


The while loop below iterates through all the lines in the origianl text file and adds the data to the variable strData:

While EOF(1) = False
    'read the current line of text
    Line Input #1, strLine
    'add the current line to strData
    strData = strData & strLine & vbCrLf
Wend


The line below concatenates the current line in the original text file with the string in strData. It also concatenates a vbCrLf to the end of the line so a new line is created. For more information about concatenating string variables please see VBA Excel String Processing and Manipluation:

strData = strData + strLine & vbCrLf


The line below concatenates the string “Data to be appended” with the variable strData:

strData = strData + "Data to be appended"


The lines below create a new text file, fills the new text file with the variable strData and overwrites the original file:

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


Example 2, Using Dialogs:

This example adds 2 dialogs to the previous example:

1. Open File Dialog
2. Input Dialog

The open file dialog prompts the user to select the location for the input file. The input dialog prompts the user to input the data to be appended to the text file. For more information about the open file dialog please see Excel VBA Open File Dialog:

Sub Example2()
Dim strData As String
Dim strLine As String
'the path of the original text file
Dim strPath As String
'the open file dialog's result
Dim intResult As Integer
'the data to appended to the text file
Dim strDataToAppend As String

'disallow user from selecting multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'remove all previous file filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'apply new file filter
Call Application.FileDialog(msoFileDialogOpen).Filters.Add("Text " & _
"File", "*.txt")
'dispaly the dialog
intResult = Application.FileDialog(msoFileDialogOpen).Show
'check user action
If intResult <> 0 Then
    'original file path
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    strData = ""
    Open strPath For Input As #1
    While EOF(1) = False
        Line Input #1, strLine
        strData = strData + strLine & vbCrLf
    Wend
    'get data to apppend
    strDataToAppend = InputBox("Data to Append")
    strData = strData + strDataToAppend
    Close #1
    Open strPath For Output As #1
    Print #1, strData
    Close #1
End If
End Sub


The open file dialog is initially displayed:

After the text file is opened and its lines are stored in the variable strData an input dialog is displayed, prompting the user to input the data to be appended to the original text file:

Result:

You can download the file and code related to this article here. Please note that although the code is in an Excel workbook, it is applicable to all MS Office products.

See Also:

• VBA, Modify Existing Text File
• Excel VBA Reading Text Files
• Excel VBA Writing to a Text File
• VBA Excel String Processing and Manipluation

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