Forum Discussion
Combine data from multiple excel workbooks in a folder into one master file
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: https://1drv.ms/f/s!Aqv4zBSFNKaymn_m8d9SM-0VmzlB?e=upFdiI
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.
11 Replies
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=T0IglGalex_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.
- 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.alex_n Difficult to follow what you are trying to achieve. Can you share a file?
- Interesting! Never thought from this perspective. Will definitely give it a try and will let you know. Thanks.
