Combining text files

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
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

Combine text files from an Excel list

Comb-Text input and output with separator line

Comb-Text input and output with separator line

This entry was posted in Excel, VBA. Bookmark the permalink.

10 Responses to Combining text files

  1. JP says:

    You can fix the “smart quotes” with a plugin:

    Also, can’t you read the entire file in one line? Something like

    Dim FileNum As Long
    FileNum = FreeFile
    Open FName For Input As #FileNum

    Dim GetText As String
    GetText = Input$(LOF(FileNum), FileNum)


  2. dougaj4 says:

    JP – thanks a lot for the tips. It seems that the plugin will only work for externally hosted blogs, but I’ll bear it in mind for if/when I can use it.

    I’ll have another look at the combine routine and re-post when I get some time.


  3. JP says:

    If you can edit your functions.php, add this line at the bottom:


  4. JP says:

    The PHP I posted got removed, hope this works:

    <?php remove_filter(‘the_content’, ‘wptexturize’); ?>


  5. dougaj4 says:

    Jimmy – I have modified the code based on what you suggested.

    I’m not sure where to find my functions.php file, but it looks like I can’t edit it without upgrading anyway.


  6. JP says:

    I guess you can’t do it with a blog, you have to have a self-hosted WordPress blog. 🙄


  7. pythonisms says:

    in cmd.exe… browse to correct folder, open a prompt, and :

    copy *.txt filename.txt


  8. JP says:


    Your code assumes the user wants to combine all the text files in a folder, not just the ones listed on the worksheet.

    But — here’s how you’d implement this in VB:

    Sub test()
    Dim x As Double
    x = Shell(“cmd.exe /c copy C:\*.txt C:\filename.txt”, 0)
    End Sub


  9. dougaj4 says:

    pythonisms – OK, I suppose “copy *.txt filename.txt” isn’t all _that_ obscure 🙂

    But for selected files the VBA would be a bit quicker, and if nothing else it did give me an excuse to look at handling of text files in VBA.

    Incidentally, for those who do want to do this sort of thing via the command line, Total Commander (or one of its clones) is an excellent tool because it includes both a graphical interface and an integrated command line.

    Jimmy – yes looks like that, but thanks for your efforts anyway.


  10. Pingback: Importing Text Files; Unix Format | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.