Reusing Combine Folder Queries

[Jonny1010101010]

Hi all,

I've started creating a file which pulls information from several workbooks in a folder location and transforms them.

The source files are of identical format but populated differently. Furthermore, there are 4 worksheets in each workbook with identical layout. Examples are Kgs, Revenue, COS and GM.

Ive written a query which takes all th Kgs tabs from all workbooks and consolidates, which works fine
However, Ive now written 4 sets of these queries for each of the Kgs, Revenue, COS and GM tabs, even though the process is essentially identical between all of them.

My question - I feel like Im duplicating computation unnecessarily - is it possible to run the Combine Folder query for multiple tab sources all in one go?

Thanks in advance!!

Sent from my ONE A2003 using Tapatalk

 

[Ken Puls]

Hi Jonny,

When you use the Combine Files feature, and you click the double down arrow to combine the files, it let's you choose a worksheet. What isn't obvious is that you can choose the Folder icon instead. This takes you to a table with all worksheets contained in the Content column. Make sure you filter to one type of object (Sheet, NamedRange or table) at this point. Then, if you expand that column in the Sample Transform, then you'll get the contents of all worksheets in the file.

Hope that helps,

 

[Jonny1010101010]

Thanks Ken! Im going to test this out tomorrow and tell you how it goes

Jonny

Sent from my ONE A2003 using Tapatalk

 

[bdarbonneau]

You may find this article useful.