June 11, 2020
I am building a 'From Folder' query that needs to pull one table from one tab on each of the spreadsheets in the folder.
There are approx. 20 spreadsheets in the folder (.xlsb format and I am using Excel 2013), the table has the same uniform layout in every spreadsheet and the tab has the same name. So far so good.
The tab in question has several pieces of data on it and the one I need is in the middle of the sheet. So, in order to make this work I defined the table I needed as an Excel Table (in every spreadsheet, same name) so that I could point the query at it.
1st problem: The query is not picking up the Table at all - i.e. it doesn't appear in the list of tabs/table/ranges, etc.
2nd problem: Is there any way that I can get the query to ignore all of the other tabs from the off, in order to speed it up? At the moment it takes an age to refresh or if I go to edit query, as it insists on cycling through every tab on every spreadsheet first even though only 1 (out of approx. 20) are being used for the actual query.
Any help would be greatly appreciated.
July 16, 2010
1. Not sure why it's ignoring the defined tables.
2. In this tutorial on getting multiple files containing multiple sheets using Power Query, you can filter out the sheets you don't want in a step prior to the 'Expanded Custom.Data' step/after the 'Removed Other Columns1' step.
Hope that helps.