Append Text from One Text File to Another Using the FileSystemObject

The following macro appends text from one text file to another using the FileSystemObject. Note that you'll need to set a reference to Microsoft Scripting Runtime by using Tools > References in the Visual Basic Editor (Alt+F11). Also, if the specified destination file doesn't exist, one will be created.

'Force the explicit declaration of variables
Option Explicit

Sub AppendFile()

    'Set a reference to Microsoft Scripting Runtime by using
    'Tools > References in the Visual Basic Editor (Alt+F11).

    'Declare the variables
    Dim oFSO As Scripting.FileSystemObject
    Dim oSourceTS As Scripting.TextStream
    Dim oDestTS As Scripting.TextStream
    Dim sSourceFile As String
    Dim sDestFile As String
    Dim sLineOfText As String
    
    'If an error occurs, go to ErrHandler
    On Error GoTo ErrHandler
    
    'Assign the path and name of the source file to a variable
    sSourceFile = "C:\Users\Domenic\Documents\TextFile1.txt"
    
    'Assign the path and name of the destination file to a variable
    sDestFile = "C:\Users\Domenic\Documents\TextFile2.txt"
    
    'Create an instance of the FileSystemObject
    Set oFSO = New FileSystemObject
    
    'Open the source file
    Set oSourceTS = oFSO.OpenTextFile( _
          Filename:=sSourceFile, _
          IOMode:=ForReading, _
          Create:=False)
    
    'Open the destination file
    Set oDestTS = oFSO.OpenTextFile( _
          Filename:=sDestFile, _
          IOMode:=ForAppending, _
          Create:=True)
    
    'Include the following line if the first line of the source
    'file is a header row and you don't want to append it to
    'the destination file:
    'sLineOfText = oSourceTS.ReadLine
    
    'Read each line of the source file and append it to the destination file
    Do Until oSourceTS.AtEndOfStream
        sLineOfText = oSourceTS.ReadLine
        oDestTS.WriteLine sLineOfText
    Loop
    
    'Close the source file and destination file
    oSourceTS.Close
    oDestTS.Close
    
    'Clear the objects from memory
    Set oFSO = Nothing
    Set oSourceTS = Nothing
    Set oDestTS = Nothing
    
    MsgBox "Completed...", vbInformation
    
    Exit Sub
    
ErrHandler:
      MsgBox "Error " & Err.Number & ": " & Err.Description
        
End Sub

Where to Put the Code

  1. Open the workbook in which to store the code.
  2. Open the Visual Basic Editor (Alt+F11).
  3. Insert a standard module (Insert > Module).
  4. Copy/paste the above code into the module.
  5. Return to Microsoft Excel (Alt+Q).
  6. Save the workbook.

How to Use the Macro

  1. Display the Macro dialog box (Alt+F8).
  2. Click/select the macro called "AppendFile".
  3. Click/select "Run".

Sample Workbook: Download

Home | Excel | VBA | Blog | About | Contact