You are currently offline, waiting for your internet to reconnect

Macro to Append Text from One Text File to Another

This article was previously published under Q141509
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SUMMARY
The "More Information" section of this article contains sample MicrosoftVisual Basic for Applications macros (Sub procedures) that you can use toappend the contents of one text file to another.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. In the following macro examples, the AppendFiles1 and AppendFiles2 macrostake the data from one text file, Source.txt, and append it to the data inthe Dest.txt text file.

To set up the example, use a text editor such as Windows Notepad to createthe 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				
The Source.txt file should contain the following information:
   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, andPrint 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 Excelfor Windows or Windows 95.

The following example runs the MS-DOS "copy" command to append two textfiles.
   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				
NOTE: When you use the Shell command to execute a command in the commandinterpreter (Command.com), use the /c switch followed by the command. The/b switch can be used with the Copy command to indicate that an end-of-filecharacter should not be added to the destination file.

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 aboutwriting data to files, click the Index tab in Microsoft Excel Help, typethe following text
Files, Writing Data
and then double-click the selected text to go to the "Writing Data toFiles" topic.

In Microsoft Excel 5.0, for more information about "Writing Data to Files"choose the Search button in Help, and type:
Writing Data
Choose the Show Topics button, select the topic, and choose Go To.

For additional information about getting help with Visual Basic forApplications, please see the following article in the Microsoft KnowledgeBase:
163435 VBA: Programming Resources for Visual Basic for Applications
5.00a 5.00c XL
Properties

Article ID: 141509 - Last Review: 10/11/2006 02:27:04 - Revision: 2.3

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • kbdtacode kbhowto kbprogramming KB141509
Feedback