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.
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
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog