How to combine multiple spreadsheets from multiple folders into one worksheet?

Harry Lee helped me with a macro very similar to the one I am now trying to put together. I get orders from 3 customers in .csv. I put these orders into their individual folders on my C drive. I am looking for a macro that will go look at all the .csv's in each of the 3 folders, then combine all the data on the .csv's into one worksheet. The .csv's have column headers. All the column headers for each customers .csv is the same, but one customer had a couple additional columns i don't need. You will see on the attached example. The attached example shows what the order looks like from each customer when sent to me. Instead of attaching 4 csv's, one for each of the 3 customers and the combine one, i just put 3 tabs, acting as 3 individual .csv's for my 3 customers. Then the 4th tab is how I am hoping it will look when all the orders in the 3 folders are combined. I made notes on the attached example as well. Thanks!!!
Combine-Orders.xlsx
brasimanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slubekCommented:
Just my three cents:
I don't think Excel is proper tool for combining multiple tables into one. It would be a lot of programming (reading files from each directory, splitting lines into columns, putting these values into proper worksheet columns etc.)
Instead, I would import these files (or link them) into three tables in Access, and then export to Excel view containing UNION of all of them.
Its just an idea, but if you will not get any help from another Experts, and decide to give it a try, let me know.
0
Ejgil HedegaardCommented:
It is not that difficult to import text files into Excel, when the folders and file specifications are known.

Try attached file.
Combine-Orders.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brasimanAuthor Commented:
Ejgil, thats perfect. Thank you!!!!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

brasimanAuthor Commented:
Awesome, thank you!!
0
brasimanAuthor Commented:
I am getting an error when running it. When i click Debug, this is highlighted:
 With wsData.QueryTables(1)
0
Ejgil HedegaardCommented:
What have you changed since the first try?
0
brasimanAuthor Commented:
I copied the macro to a different spreadsheet. I am assuming the error on QueryTables(1) is because it is referring to a table that is not there anymore from the one you created for me. Where do I find where that table is named QueryTables(1) on the one you sent me? Then I can change it on my other spreadsheet. Thanks!!!
0
brasimanAuthor Commented:
I can't find this anywhere. Why does the one you sent me work? I copied the macro exactly as is, the folders are all setup properly. Its just this one error. Any help is appreciated!!
0
brasimanAuthor Commented:
Here's a screenshot of the error.
Error.png
0
Ejgil HedegaardCommented:
QueryTables(1) means query table number 1 on the sheet, it is not the name of the table.

In the file there is a hidden sheet named Data, right click on the sheet name "Final Result - Combined", and select show.

Insert a sheet in your workbook, name it Data, and make a text import of one of the files, starting in A1. Go to the Data tab, and select "From text" to start the text import wizard.
Then right click somewhere in the table, select Settings, and remove mark for "Ask for filename"

Or copy the Data sheet to your new workbook. Right click the sheet name, and select "Move or copy".
Select the new workbook in the upper box, and tick "Make a copy" at the bottom.

Both sheet names "Final Result - Combined" and Data are used in the macro.
If you use other sheet names, the macro must be adjusted to that.
Replace the names in these 2 lines,
Set wsResult = Worksheets("Final Result - Combined")
Set wsData = Worksheets("Data")
0
brasimanAuthor Commented:
Thanks for your quick reply!! I tried both ways, and i still get the same error. :(
0
brasimanAuthor Commented:
Any other ideas?
0
Ejgil HedegaardCommented:
Not without seeing the workbook.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.