Microsoft KB Archive/213642

= XL2000: Macro to Append Text from One Text File to Another =

Article ID: 213642

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213642





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 professionals 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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

In the following macro examples, the AppendFiles1 and AppendFiles2 macros take the data from one text file, Source.txt, and append it to the data in the Dest.txt text file.

To set up the example, use a text editor such as 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 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 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 After you run the 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

