Avatar of Don OHara
Don OHara
 asked on

How to use VBA to combine similarly formatted single sheet Excel files.

I have a series of sets of Excel files. In each set, I have a group of Excel files all of the same format (same number of columns). All are single sheet outputs. I need to combine these into a single sheet excel file.  

I need to add in the name of each file in a new column after column 1, on each line. This way I know the source of the data.

I will have a directory in which are all of the source files (no other files)of one set. I want to place in this directory a NEW excel file containing the VBA that will combine these files into the NEW excel file.  

I have attached four source Excel files and a copy of the solution Excel file. I use excel 365 on Windows `10DonOHara.zip and Windows 7.

Thank you,
Don OHara
Windows OSVBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Tom Farrar

8/22/2022 - Mon
Peter Chan

1. You need to scan the folder for relevant Excel files (or if you have the list of all relevant Excel files).

2. Copy the whole sheet into the "Main" Excel file. See Extract below
-Quote-
Copy Worksheet to Another Workbook
So far we’ve worked with copying Sheets within a Workbook. Now we will cover examples to copy and paste Sheets to other Worbkooks. This code will copy a Sheet to the beginning of another workbook:
1      Sheets("Sheet1").Copy Before:=Workbooks("Example.xlsm").Sheets(1)
This will copy a Worksheet to the end of another Workbook.
1      Sheets("Sheet1").Copy After:=Workbooks("Example.xlsm").Sheets(Workbooks("Example.xlsm").Sheets.Count)
-Unquote-
From this URL

https://www.automateexcel.com/vba/copy-worksheet/#Copy_Worksheet_to_New_Workbook
Tom Farrar

Would you consider using something other than VBA?  Excel had a tool (Power Query/Get and Transform) that can combine Excel files within the same folder into one data set.  An advantage of this approach would be as more files are added to the folder the data will update.  Pretty sure the file name (or tab name) can be added into the records.
ASKER CERTIFIED SOLUTION
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Tom Farrar

Here is the result from combining your sample files using Power Query
Combined.xlsx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Don OHara

ASKER
Syed,
Thanks you for your timely and direct solution for what I asked for. I am able to quickly continue my project. It also works for CSV files?  Thanks.


Tom. Thanks for your suggestions. I am always looking for better tools. I will check into Excel Tools.

Peter,  Thank for responding and your suggestions. I did take a look at your idea, but I was not able to grasp it quickly, and I needed to get my project moving.  

Thanks everyone.
Don
Tom Farrar

Thanks for the points, Don.  You should check out the Power Query tool.  It is embedded in Excel 2016 forward.  Very useful for solutions that only could have been with significant manual work, or only achieved with VBA.
Don OHara

ASKER
Thanks Tom.
Just read an intro and it does look good. I will find more complete write ups.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Tom Farrar

There are also some great videos.  For example, this one is probably addresses porblems similar to your issue.

https://www.youtube.com/watch?v=OSCPVBWOqwc