VBA Append Text Files

VBA Append to Text File using two statements. It is almost same like Write date to text files. There is only one difference between Writing and appending data to text files. Just need to replace in Write or Print statement with append instead of Input or mode mode. The first statement is Write or the second statement is Print. The Write statement generates a line of values and it is separated by commas, and adds hash marks(#) around dates and quotes(“) around strings. The Print statement generates a line which is suitable to be printed, with the data is arranged in columns with spaces between. Let us learn syntax and examples on Write and print statements.

Table of Contents:

Objective
Syntax for Append to Text File
Appending data to Text File using Write Statement
Appending data to Text File using Print Statement
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax for Append to Text File

You can find following syntax for Append to Text File in Excel VBA.
Syntax:

Write #FileNumber, Input_Value1, Input_Value2, ...
'or
Print #FileNumber, Line_Input_String

Where FileNumber represents the unique number to file and contains numeric value. It helps to open text files.
Input_Value1, Input_Value2, … represents the input data which you want to Append to Text File. All values are separated by commas, and it adds hash marks(#) around dates and quotes(“) around strings while Appending to text file.
and Line_Input_String represents the input string to Append to Text File.

Appending to Text File using Write Statement

You can find following VBA Code. It helps to Append to Text Files using Write statement in Excel.

'Appending data to Text File using Write Statement
Sub VBAF1_Append_Text_File_Using_Write_Statement()
    
    'Variable Declaration
    Dim sFilePath As String
    Dim iStRow As Integer, iLstRow As Integer
    Dim dDate As Date, sCountry As String, sRegion As String, iUnits As Integer
   
   'Specify Text File Path
    sFilePath = "C:\VBAF1\FirstTextFile.txt"
        
    'Get Unique File Number using FreeFile
    fileNumber = FreeFile
    
    'Check Specified Text File exists or not
    If VBA.Dir(sFilePath) = "" Then MsgBox "File Does not exists": End
    
    'Open TextFile in Append mode
    Open sFilePath For append As #fileNumber
    
    'Find Last Row
    iLstRow = Sheets("Input Data").Cells.SpecialCells(xlCellTypeLastCell).Row
        
    'Loop through all rows in Worksheet
    For iStRow = 2 To iLstRow
        'Append Data to Text File
        With Sheets("Input Data")
            dDate = .Cells(iStRow, 1).Value
            sCountry = .Cells(iStRow, 2).Value
            sRegion = .Cells(iStRow, 3).Value
            iUnits = .Cells(iStRow, 4).Value
        End With
    
        'Write Data to Text File
        Write #fileNumber, dDate, sCountry, sRegion, iUnits
       
    Next
    
    'Close Text File
    Close #fileNumber
        
End Sub

Output: You can find following Input and output screenshot for your reference.

Appending to Text File using Print Statement

You can find following VBA macro. It helps to Append to Text Files using Print statement in Excel.

'Appending data to Text File using Print Statement
Sub VBAF1_Append_Text_File_Using_Print_Statement()
    
    'Variable Declaration
    Dim sFilePath As String
    Dim iStRow As Integer, iLstRow As Integer
    Dim sDataLine As String
   
   'Specify Text File Path
    sFilePath = "C:\VBAF1\FirstTextFile.txt"
        
    'Get Unique File Number using FreeFile
    fileNumber = FreeFile
    
    'Check Specified Text File exists or not
    If VBA.Dir(sFilePath) = "" Then MsgBox "File Does not exists": End
    
    'Open TextFile in Append mode
    Open sFilePath For append As #fileNumber
    
    'Find Last Row
    iLstRow = Sheets("Input Data").Cells.SpecialCells(xlCellTypeLastCell).Row
        
    'Loop through all rows in Worksheet
    For iStRow = 2 To iLstRow
        'Append Data to Existing Text File
        With Sheets("Input Data")
            sDataLine = Format(.Cells(iStRow, 1).Value, "dd-mmm-yyyy") & ";"
            sDataLine = sDataLine & .Cells(iStRow, 2).Value & ";"
            sDataLine = sDataLine & .Cells(iStRow, 3).Value & ";"
            sDataLine = sDataLine & .Cells(iStRow, 4).Value
        End With
    
        'Write Data to Text File
        Print #fileNumber, sDataLine
       
    Next
    
    'Close Text File
    Close #fileNumber
        
End Sub