Forum Discussion

Brass Contributor

Jun 12, 2023

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...

Platinum Contributor

Jun 13, 2023

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/

alex_n

Brass Contributor

Jun 14, 2023

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.

Riny_van_Eekelen
Platinum Contributor
Jun 14, 2023
alex_n Difficult to follow what you are trying to achieve. Can you share a file?
- alex_n
  Brass Contributor
  Jun 20, 2023
  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,
  - Riny_van_Eekelen
    Platinum Contributor
    Jun 20, 2023
    alex_n Will get back to you tomorrow, if not someone else jumps in.