July 11, 2016
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?
July 16, 2010
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:
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.
Most Users Ever Online: 57
Currently Online: KingTamo
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas