Querying information from multiple excel files into a single sheet

Copper Contributor

Hi Everyone,

 

I am trying to pull certain relevant data from multiple excel files that are located in multiple places into a single sheet on a new excel file. The files are not located in the same place because they are in their respective job folder. The data that I am trying to pull in should update automatically when updated in the original file. 

 

If anyone has any advise I would appreciate it. If I was unclear or anyone has questions feel free to ask.

 

Thank you! 

7 Replies

Rebecca, that would be a job for Power Query.

 

Google Search

 

I did find the Power Query option on the internet when I was searching. I wasn't really sure how to do this when the files are all in different folders on the server. The examples I saw had all the excel files in a single folder. 

It is the same when importing a single file. You only you have to do it multiple times.

 

@Detlef Lewin curious where/how in Power Query you can specify more than the first worksheet? I have Power Query working on 40 files in a single folder, but I want to include information from multiple worksheets in EACH file.

 

@whiskeydude 

If you use From Folder connector you specify first file (or any other one on your choice) as the sample. Based on it connector takes information from all files in the folder and combine it together. All these files shall have the same structure. More exactly, portion of information you combine. If you selected Table1 or Sheet1 for the sample file, from all other files it will be taken only Table1 or Sheet1. Within these objects small deviation in structure is possible, but it depends.

thanks @Sergei Baklan - got the 1st part working (pulling 30+ files into a single sheet in one file. NOW, it gets tricker. I have a second sheet (same structure as the first) in just some of the 30 files. I figuyred out how to get all this data pulled into the same file, but Excel puts all this info in a second sheet, not the primary. I'm guessing I'm just missing a setting somewhere. Any clues on how to "merge" two sheets in 30 files into a single sheet in one file?

@whiskeydude 

If Query1 collects data from Sheet1 of all files, and Query2 - from Sheet2, load these queries as connections only. After that in Power Query editor append one query to another and load result into Excel sheet.

 

Assuming all sheets have the same structure.