Combine all text files in folder into one.

Charleton

New Member
Joined
Aug 30, 2017
Messages
23
I can make my request easier known by showing how I know to do something:

From the windows command prompt, I can type

Code:
cd C:\strFolderName\strSubFolderName\
and it will change to the directory.

then if I type

Code:
Copy *.txt ALL.txt
When you do this it will make a new .txt file called "All.txt" that will contain all of the data from every text file in the folder it is looking in.
It will also retain all my original files.

My question is how can I do this from in Excel with VBA?


Thanks for any help you have in this!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
Hi Charleton

You certainly can do it with VBA, but it would require a lot of coding. Far easier (and faster - by a large factor) to do it with Power Query.

In the past I'd immediately tackle something like this with VBA, but now it is all the way with PQ when I can.

Google "power query combine multiple files" and have fun, while being totally amazed at how powerful Power Query is!

Cheers

pvr928
 
Last edited:

Charleton

New Member
Joined
Aug 30, 2017
Messages
23
Hello, and thank you for your advice, that could be one of the things I have never seen. (Get & Transform in 2016, but WOW!)

One point of note, this is not importing the data into excel, though. I just want the files to be consolidated into a new text file, and that put in the same folder for a database to access.
So far, it seems like this imports the data to excel.

Thanks for the tip, though!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,410
Office Version
365
Platform
Windows
One way would be to create a batch file which does what you want, i.e.
Code:
cd C:\strFolderName\strSubFolderName\
copy *.txt ALL.txt
and let's name it CombineText.bat

Then use the Shell command in VBA to call and run your batch file, i.e.
Code:
Sub MyCombineText()
    Shell "C:\myFolderName\CombineText.bat"
End Sub
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,263
You can do it in a single DOS command from VBA by specifying the folder path on both the copy command arguments:
Code:
Public Sub Test()

    Dim folderPath As String
    Dim DOScommand As String
    
    folderPath = "C:\folder\subfolder\"
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    DOScommand = "COPY " & folderPath & "*.txt " & folderPath & "ALL.txt"

    Shell Environ("comspec") & " /c " & DOScommand, vbHide
           
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,098,860
Messages
5,465,116
Members
406,414
Latest member
Discorz

This Week's Hot Topics

Top