I'm looking for a VBA code (macro) to merge text files.

[grid]

Hello,

I'm looking for a VBA code (macro). I want it a whole folder of text files to merge into 1 text file. So far have found nothing. Maybe someone can help me?

I want the folder in the VBA code included.


Best regards,

grid

 

[grid]

It is more a part of something big that I work. I'm doing a VBA code.

 

[Richard Schollar]

Here's one way:

Dim strPath As String

strPath = "C:\Some folder\Some other folder\"   'don't forget the closing \!

Shell "cmd.exe copy /c """ & strPath & "*.txt"" """ & strPath & "Consolidated.txt""",0

It will merge all text files in the given folder into an output consolidated file in the same folder named Consolidated.txt.

 

[kileil]

By merge you mean append?

If you're just looking to jam all text files, back to back, in one text file creating a little batch file may be an easier option than excel.

I don't have code handy, but it can't be more than a few lines. Just get code off google, paste into Notepad, save, and change extension to *.bat and run inside the folder containing the text (a copy to be safe).

 

[grid]

Hello RichardSchollar,

Thank you. I run it as a procedure. It is necessary to complement my other VBA code.

The files that make Consolidated.txt change every so often. I will merge a few more times in a while. I have not run your script. But the dos screen you can also run in the background? So that you do not see the taskbar.

I want the script to run automatically every 10 seconds via:

Sub MySub ()
Application.OnTime Now + TimeValue ( "00:00:10"), 'Merge'
End Sub

Can you also make calls through the procedure Merge?


Thank you,

grid

 

[grid]

Hello RichardSchollar,

It has been resolved. I'll figure it out.


Best regards,

grid

 

[sai85]

Hello RichardSchollar,

It has been resolved. I'll figure it out.


Best regards,

grid

Hi Grid,

Can you please share the resolved macro for us!

Regards,
Sai

 

[kcribb]

I found these on another site in case anyone is still searching for a solution.

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

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

 

[Amadea]

Hi there. I'm trying to do the same thing. I used

Shell Environ$("COMSPEC") & " /c Copy C:\temp\*.tmp C:\temp\CombinedFile.tmp"

and

Shell "cmd.exe copy /c """ & strPath & "*.txt"" """ & strPath & "Consolidated.txt""",0

As long and I use C:\test folder, everything works out just fine. But when I use this:

folder = ActiveWorkbook.Path & "\output"
Shell Environ$("COMSPEC") & " /c Copy " & folder & "\*.scr " & folder & "\CombinedFile.scr"

nothing happens. I'm not sure what I'm doing wrong here.