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. (http://www.rondebruin.nl/txtcsv.htm)

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