XL: Macro to Append Text from One Text File to Another

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SUMMARY

The "More Information" section of this article contains sample Microsoft Visual Basic for Applications macros (Sub procedures) that you can use to append the contents of one text file to another.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

To set up the example, use a text editor such as Windows Notepad to create the following text files.

The Dest.txt file should contain the following information:

   Last Name, First Name, Address, City, State, Zip Code
   John,Smith,1 Main St.,Seattle,WA,12345
   Jane,Doe,2 East Rd,Charlotte,NC,52631
   Tom,Jones,3 West Cir.,Tuscon,AZ,36258

   Bill,Adams,6 Elm Ln.,Seattle,WA,85692
   Sue,Thompson,33 Maple Ct.,Las Colinas,TX,58692
   Jerry,Brown,32 Main Rd.,Tuscon,AZ,96524
   Pamela,Smith,88 Rain Rd.,Charlotte,NC,89526

Method 1

Use file input and output (file I/O) commands in a Visual Basic macro.

The following macro demonstrates how you can use the Open, Line Input, and Print statements to read data from one text file and append it to another.

   Sub AppendFiles1()

      Dim SourceNum As Integer
      Dim DestNum As Integer
      Dim Temp As String

      ' If an error occurs, close the files and end the macro.
      On Error GoTo ErrHandler

      ' Open the destination text file.
      DestNum = FreeFile()
      Open "DEST.TXT" For Append As DestNum

      ' Open the source text file.
      SourceNum = FreeFile()
      Open "SOURCE.TXT" For Input As SourceNum

      ' Include the following line if the first line of the source
      ' file is a header row that you do now want to append to the
      ' destination file:
      ' Line Input #SourceNum, Temp

      ' Read each line of the source file and append it to the
      ' destination file.
      Do While Not EOF(SourceNum)
         Line Input #SourceNum, Temp
         Print #DestNum, Temp
      Loop

   CloseFiles:

      ' Close the destination file and the source file.
      Close #DestNum
      Close #SourceNum
      Exit Sub

   ErrHandler:
      MsgBox "Error # " & Err & ": " & Error(Err)
      Resume CloseFiles

   End Sub

Method 2

Use the MS-DOS "copy" command. This method applies only to Microsoft Excel for Windows or Windows 95.

The following example runs the MS-DOS "copy" command to append two text files.

   Sub AppendFiles2()

      ' Run the DOS Copy command to append the text files.
      Shell "command.com /c" & _
         "copy c:\dest.txt+c:\source.txt c:\dest.txt /b"

   End Sub

After you run either macro, Dest.txt should resemble the following:

   Last Name, First Name, Address, City, State, Zip Code
   John,Smith,1 Main St.,Seattle,WA,12345
   Jane,Doe,2 East Rd,Charlotte,NC,52631
   Tom,Jones,3 West Cir.,Tuscon,AZ,36258
   Bill,Adams,6 Elm Ln.,Seattle,WA,85692
   Sue,Thompson,33 Maple Ct.,Las Colinas,TX,58692
   Jerry,Brown,32 Main Rd.,Tuscon,AZ,96524
   Pamela,Smith,88 Rain Rd.,Charlotte,NC,89526

REFERENCES

In Microsoft Excel for Windows 95 version 7.0, for more information about writing data to files, click the Index tab in Microsoft Excel Help, type the following text

   Files, Writing Data

In Microsoft Excel 5.0, for more information about "Writing Data to Files" choose the Search button in Help, and type:

   Writing Data

For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q163435
   TITLE     : VBA: Programming Resources for Visual Basic for
               Applications