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?
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.
Get the VBA Toolbelt!
Quit digging through old projects and forums like a chump! Download the VBA Toolbelt and start with the most common Excel tasks already done for you.