Combining Many Excel CSV Files Into One Part 3

What should you do when you need to combine many Excel files (CSVs, XLSXs, TXTs, whatever) into one “master” file?

Given this file structure with CSVs, we want to merge them all into the master file

Part 3 of a 3 Part Series (Part 1) (Part 2)

We’re almost there y’all! After wrapping Part 2, you now all the data in a single sheet. All that’s left to do is move all the processed CSVs to the “processed” folder (duh). Let’s get cracking!

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

This one is short and sweet, but still sticks to the 4-step VBA process:

Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup

Lines 4-9 are our straightforward Step 1 – Setup. Most important is line 9, where we use the Dir function — Dir does the heavy-lifting here.

The Dir function returns the first filename matching the pathname specified. We know that our macro-containing workbook is in the same folder as the CSVs, so we can use ThisWorkbook.Path combined with “*.csv” to catch the CSV files. (We also take advantage of the fact that Dir returns a zero-length String if there are no more matches in our While loop.)

Lines 15-18 handle our Step 2 – Exploration. We already know everything we need to in this particular case, so we simply assign filenames — nice! strDestinationFile is exactly the same as strSourceFile, except strDestinationFile has had the “processed” folder injected into the middle.

Step 3 – Execution is accomplished by lines 21-24. Moving files with VBA is a cinch by using the Name function, and on the next line we increment strFile by calling Dir again.

Finally, Step 4 – Cleanup is simply a notification to the user on line 28 that the script is finished.

Now that we’ve built everything out, our Public Sub ImportDataThenMoveFiles() script does it all!

Is this import-the-CSVs-then-move-the-CSVs suite of macros working for you? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.