Merge multiple .txt files into one .txt thru Excel VBA

[xlml]

Hello,
I would like to merge all the .txt files in filepath C:\TEST into one .TXT file which saves into the same filepath. I'd like to do this thru XL VBA but have the files remain .TXT and append as well.

Any help?
Thanks

[Jan Karel Pieterse]

You could use something like this:

Sub CombineTextFiles()
    Dim lFile As Long
    Dim sFile As String
    Dim vNewFile As Variant
    Dim sPath As String
    Dim sTxt As String
    Dim sLine As String
    With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show Then
        sPath = .SelectedItems(1)
        If Right(sPath, 1) <> Application.PathSeparator Then
            sPath = sPath & Application.PathSeparator
        End If
    Else
        'Path cancelled, exit
        Exit Sub
    End If
    End With
    vNewFile = Application.GetSaveAsFilename("CombinedFile.txt", "Text files (*.txt), *.txt", , "Please enter the combined filename.")
    If TypeName(vNewFile) = "Boolean" Then Exit Sub
    sFile = Dir(sPath & "*.txt")
    Do While Len(sFile) > 0
    lFile = FreeFile
    Open CStr(sFile) For Input As #lFile
    Do Until EOF(lFile)
        Line Input #1, sLine
        sTxt = sTxt & vbNewLine & sLine
    Loop
    Close lFile
    sFile = Dir()
    Loop
    lFile = FreeFile
    Open CStr(vNewFile) For Output As #lFile
    Print #lFile, sTxt
    Close lFile
End Sub

[snb]

or

Sub snb()
c00 = "C:\Test\" ' the path
c01 = Dir(c00 & "*.txt")
Do Until c01 = ""
    c02 = c02 & vbCrLf & CreateObject("scripting.filesystemobject").opentextfile(c00 & c01).readall
    c01 = Dir
Loop
CreateObject("scripting.filesystemobject").createtextfile(c00 & "_new.txt").write c02
End Sub

[Tinbendr]

Also here.

[shrivallabha]

You can employ a DOS command through VBA as well.

Public Sub CombineFiles()
Shell Environ$("COMSPEC") & " /c Copy C:\TEST\*.txt C:\TEST\CombinedFile.txt "
End Sub

[Rick_R]

Clarification about calling DOS, since some folks have pointed out on other forums that there are problems if path names contain spaces.

Create the string as if it was being entered on the command line, including necessary double quotes. Then double each double quote. Then put one additional double quote at the beginning and one additional double quote at the end.

The command below basically says "combine all text files matching the pattern into cmnt-all.txt". Because the code line could get extremely long and confusing I find it easier to put the source and target paths in variables.

Note that the target string begins with a space, followed by the path's starting quote.

Incidentally, this was tested using TCC.exe from JPSoft, not cmd.exe, so the command syntax might be slightly different.

In case you're not familiar with TCC.exe, it's basically "CMD.EXE on steroids". A much older version was included in Norton Utilities under the name NDOS. If Microsoft had continued actively developing CMD.EXE for 35+ years, TCC and TCMD (full IDE, etc.) are what it would look like.

Sub Merge_TXE_comments()
    Dim src_pat As String
    Dim target As String
src_pattern = """F:\CLIENT STUFF\TXE\a-Temp\cmnt-*.csv"""
    target = " ""F:\CLIENT STUFF\TXE\a-Temp\cmnt-all.txt"""
Call Shell(Environ("COMSPEC") & " /c copy " & src_pattern & target, vbMinimizedNoFocus)
End Sub

[SamT]

Thank you, Rick.

But, now, I'm, closing this 5yo thread.

Any other question about this will have to be in a new thread.