Abdullah Kavi (with some help from Bob Archer) from the Excel Test Team has taken Boaz’s solution for converting query tables so that their data can be consumed in Excel Services and added quite a few features to it.
In his own words:
I have created an Add-In based on Boaz’s original add-in and this will convert QueryTable to a refreshable Table and the Table can be refreshed in Excel Services when you select “Refresh All Connections” and live data will be shown on the table. It also has an option to convert to a PivotTable.
The UI dialog looks like this:
When you press “More Option >>”, it looks like below:
When you convert a QueryTable to a PivotTable and add fields to the row area, you might end up with lots of blank cells in the PivotTable and you don’t really get all “Table” functionalities.
Using this Add-In you can overcome both the problems. The idea behind this conversion is that the tool creates a PivotTable first, then it creates a table using formulas that point to PivotTable cells. As PivotTable gets updated, new values are shown correctly on the PivotTable and on the table.
Since PivotTable may grow or shrink as the back end table may increase or decrease, you have the option to set Table size as appropriate during conversion. It is suggested that you add some more rows while you convert.
Since this add-in has the option of converting to PivotTable too, you can also choose that if you think that’s appropriate for you. The UI gives you options to choose create refreshable table at the original location or a new sheet, copying source QueryTable to a new workbook or to delete that, insert rows below or overwrite data below when refreshable table grows and to apply current or default table style.
There are a few limitations to the add-in. It should be easy enough to fix these issues with a little VBA:
1. If the workbook contains more than one QT this won’t work (one can only transform a single Query Table.
2. If any sheet name “Pivot_Sheet” already exists this won’t work
3. If any sheet name “Table_Sheet” already exists this won’t work
4. If any already defined name “RecCnt” exists, this won’t work
5. If any sorting applied already, that will be gone, but you can apply later
6. If any filtering applied already that will be gone, but you can apply later
Update: Added a new version of the tool that solves a small issue.