Combine data from multiple excel workbooks in a folder into one master file

[alex_n]

I use Office 365

Hello,

Please note that I made few changes to the criteria and the files that correspond to the new changes.

I need help with VBA code that need to perform the below:

There are reports (excel files) with different file names but all have 'summary' tabs with the same column names. The reports are stored in a folder where the master file (Summary Master) needs to retrieve the data from certain columns from 'summary' tab of each file and combine all into 'summary master' tab of summary file.

Here is the link to the example files: Summary

1. Summary Master.xlsm - master file with 'Summary Master' tab. This file performs the combining

2. Reports - folder where all reports are stored

A few things to note:

a. number of files is not set, it will increase by day

b. report file has multiple tabs

c. when a new report file is added to the Reports folder, the summary needs to append the new data

Thank you in advance.

[#2]

@alex_n Sounds like a job for Power Query. No VBA needed. The link below take you to a site that's a great place to start learning. When you come to section 9, you'll learn all about connecting to files in a folder.

https://exceloffthegrid.com/power-query-introduction/ 

[#3]

https://b23.tv/jWpwmwK

How about play with sql?

[#4]

Interesting! Never thought from this perspective. Will definitely give it a try and will let you know. Thanks.

[#5]

Hi. This solution is great and so intuitive. But there's one thing that needs to be considered and it will be golden.

Going forward, they added a new column in the Report file and combining the report files result in one of the columns showing incorrect content as the data shifted to the right with the additional column and created a mess.

Consequently, I have two data sets. I will add them to the data model so I can combine them.

Also, each column is a name range. I tried to select (or pick out) certain columns I want populated but it does not let me select more than one name range. Just to test, I added two name range columns with separate steps and it resulted in tripled or quadrupled line items.

What would be a solution here to resolve this?

Thanks again.

[#6]

@alex_n Difficult to follow what you are trying to achieve. Can you share a file?

[#7]

They added a new column called Fee in reports 4 and 5.

How will the combining work if reports 3 and 4 have new column?
https://1drv.ms/f/s!Aqv4zBSFNKaymn_m8d9SM-0VmzlB?e=d5NNXl

Thanks,

[#8]

@alex_n Will get back to you tomorrow, if not someone else jumps in.

[#9]

I cannot find a way where I can populate multiple columns using the name ranges as opposed to populating the entire tab where the contents of the columns will be mixed up as new columns were added with reports 3 and 4. It only lets me populate one name range column at a time.

Here is the parameters list when I invoke custom function:

'Summary' is entire tab

'Contract' through 'Year' are name ranged columns. It is missing 'Overhead' name ranges that was added to report 4.

[#10]

@alex_n

I believe you need to work with structured tables. In all four files, I formatted each of the data ranges to an Excel table called "Table1". It should like something like this:

Then you can connect PQ to the Reports folder and pick-up Table1 from each file. Make sure that you select report 4 as the sample file. PQ defaults to the first file in the folder. If that one has fewer columns than later ones the new columns will not be picked up.

The attached file should work for you provided you create the tables as mentioned above.

[#11]

I have over 100 files that I would need to work to apply the structured tables to. Looks like a VBA approach is a better solution here.

[#12]

Can anyone assist me with a VBA code approach to achieve the result? At the link, I have posted the tables and Summary Master file as an expected outcome. It should pick certain columns (each column is name ranged) and combine all in one file. Thanks in advance.

https://1drv.ms/f/s!Aqv4zBSFNKaymn_m8d9SM-0VmzlB?e=T0IglG