Combine all text files in folder into one.

[Charleton]

I can make my request easier known by showing how I know to do something:

From the windows command prompt, I can type

cd C:\strFolderName\strSubFolderName\

and it will change to the directory.

then if I type

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!

 

[pvr928]

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

 

[Charleton]

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]

One way would be to create a batch file which does what you want, i.e.

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.

Sub MyCombineText()
    Shell "C:\myFolderName\CombineText.bat"
End Sub

 

[John_w]

You can do it in a single DOS command from VBA by specifying the folder path on both the copy command arguments:

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

 

[Charleton]

John_w, that is pure poetry.

Thank you all!