Hi,
I have a workbook that contains multiple sheets. All of the sheets have identical formats. Each sheet has a table that contains summary data. I want to combine these in to one table for all sheets. Currently I have done this by creating a connection only query for each table and then appending them to get my combined table.
From time to time new sheets will be added to the workbook and I will want to include these in to the combined sheet.
I know that if you create a query from a folder you can get it to automatically include any new files added to the folder in your query. Is there any similar functionality whereby I can automatically include any new worksheets and tables in to my combined query in the scenario above?
Thanks
Mark
Hi Mark,
You can use the Excel.CurrentWorkbook function to do this.
Create a blank query in your Excel file that contains the tables you want to combine. In the Query Editor formula bar type:
=Excel.CurrentWorkbook()
Press ENTER
This will bring up a list of all the tables, sheets and named ranges in your file. Filter the Name column to select the items you want.
Tip: name all of your tables with a common beginning or ending. e.g. I start the name of all my tables with 'tbl_...' so when I filter the list I can simply specify Text that Begins With; tbl_ and then when I add any new tables they will be included in the query.
Mynda
Thanks Mynda that solution works fine.
Hi Mynda!
So what if I add a worksheet, and its table has a column that didn't appear in any of the original tables. How do I get the query to recognize new column names? The Expanded Content step only includes the columns in pre-existing tables, even though I included all tables.
Hi Jon,
Refresh the query, then click on the cog icon beside the Expanded Content step > click 'load more' > check the box for the new column.
Chris Webb has a post here on how to use a custom function to extract a list of all column names if you want a solution that doesn't require editing the query.
Mynda
Thanks, I'll check it out.
