Newton Excel Bach, not (just) an Excel Blog

Updated 3 April 2009. Following Jimmy Pena’s comments I have revised the code to read and write the files in one operation, rather than line by line. I have also added the option to add a numbered separator line between each file in the combined file.

What is the easiest way to combine a large number of text files into a single file?

Probably the easiest way is with some obscure command line syntax, that you can never remember or find help on when you need it; but for those of us who like to things in VBA the procedure below is reasonably fast and simple to use.  The procedure is:

• Get the list of file names and location, and the name for the combined file.
• Change directory to the location of the files to be combined.
• Open the combined file.
• For each file in the list:
• Open the file.
• Read the file and write it to the combined file.
• If a separator line is specified wite the line and file number to the end of the combined file
• Close the file and open the next.
• After reading and writing all the files close the combined file.

To get the list of file names into a worksheet I use the file manager Total Commander, which lets you copy the names of selected files to the clipboard.

I have added the routine Comb_Text() to the spreadsheet Text-in2.xls, which can be downloaded here.

Here is the code:
Sub Comb_Text()
Dim i As Long
Dim FNameA As Variant
Dim NumFiles As Long, FName As String, Fnum1 As Long, FNum2 As Long
Dim Wholefile As String, FPath As String, AFname As String, SepLine As String
On Error GoTo no_selection
FNameA = Selection.Value
FPath = FNameA(1, 1)
If Mid(FPath, 2, 1) = ":" Then ChDrive Left(FPath, 1)
ChDir FPath
AFname = FNameA(2, 1)
Fnum1 = FreeFile
Open AFname For Output Access Write As #Fnum1
SepLine = FNameA(3, 1)
NumFiles = UBound(FNameA)
For i = 4 To NumFiles
FName = FNameA(i, 1)
FNum2 = FreeFile
Open FName For Input Access Read As #FNum2
Wholefile = Input$(LOF(FNum2) - 1, #FNum2)
Print #Fnum1, Wholefile
If SepLine .NE. "" Then Print #Fnum1, "End of File " & i - 3 & " " & SepLine
Close #FNum2
Next i
no_selection:
Close #FNum2
Close #Fnum1
End Sub


Note that if code is copied and pasted from the blog the “” characters get corrupted, and need to be corrected in the Visual Basic Editor, or just download the spreadsheet which has open source code.  Also replace “.NE.” with the usual VBA “not equal” symbol.

Here’s what it looks like:

Combine text files from an Excel list

Comb-Text input and output with separator line