Another tool for working around Excel Services not supporting QueryTable


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:


Overview

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.

Limitations:

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.

QueryTable Tools_Blog.zip

Comments (34)

  1. Your tool is great. It is converting the table to a refreshable query table. When I public this table in a share point server throws the error that "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:

    External data ranges (also called query tables)

    "

    Pls help in publishing this refreshable query table to share point. I need to refresh this table through excel services. How can we resolve this error.

  2. Shahar Prish says:

    Did you choose to remove the previous query-table?

    If it stays on the workbook, it will cause the error you are seeing (or if there’s a completely different one there).

  3. TS says:

    Hello, i got the table converted and it runs thru excel services fine but it won’t refresh with live data. I call es.Refresh(sessionId, string.Empty); which refreshes all the connections in the workbook and the outStatus is null which means it completed that successfully.

    Above it says that you must select “Refresh All Connections”. Is what i said above what is meant here or is there somewhere else that it is describing?

    Why won’t the data refresh?

    thanks!!!

  4. TS says:

    Sorry, but i may have spoke too soon. when i ran it in code the old value was appearing – i did it 3 times, then just after posting this i tried again and the updated value was there.

    if i have further problems i will post again – sorry to clutter up this blob, but I think that I have proven that the tool works as expected!

  5. Shahar Prish says:

    Excel Services has special admin settings which say how often a workbook may be refreshed.

    What you are seeing is that optimization in action. Your refresh didnt really refresh anything.

    For testing, you can change this setting on the Trusted Location – it should be called something like Refresh Interval or something.

  6. TS says:

    Question – why is the add-in written in VBA. i thought that excel 07 was .net compatible and so i would think that the VBA scrip would not even work, and some .net code would be appropriate.

  7. Shahar Prish says:

    Excel Client 2007 still uses the same VBA it has always used.

    By using VSTO/VSTA, you also get support for .NET in Excel (and other Office apps). That requires extra layers and installation procedures.

    This way, the add-in is encompassed in a single file and is eaiser to provide to people for usage.

  8. Geetha Boggarapu says:

    I need to find the sum of a calculated field and use this sum in another calculated field in pivot table using excel 2007. Can any one help me in find the sum of calculated field and how to use this value in another calculated field in pivot table. Pls mail me to geetab123@yahoo.com

  9. Abdullah says:

    Great that some people are finding this tool helpful!

    To answer your question:

    Whenever you create a calculated field and add that in the pivottable report, the sum of that calculated field is shown in the grand total column/row area. What exactly are you trying to do here? You can re-use the newly created calculated field as part of the formula of another calculated field.

  10. BobChauvin says:

    Anyone try this wilt Excel 2007?  I am having problems, just starting, but I get an error converting a sheet linked to a sharepoint v3 document library.

    The dialog displays, but the conversion crashes

        If table.CommandType = xlCmdTable Or table.CommandType = xlCmdSql Then

    the value of table.commandtype is 5, so …  

  11. Sasya says:

    I am getting the following error when I am trying to run the tool with Refreshable Query table option selected in the options. After the conversion I am trying to publish the the page to Excel web services and it is displaying the error message "Excel Services can not load the workbook that you have requested" in the browser. But If I try with the Pivot Table option and Publish it works fine in Excel web services.

    Any help will be appreciated.

    Thanks in Advance

  12. Abdullah says:

    Hi BobC:

    This tool was not designed to work with connection to Sharepoint Document Library.

    Hi Sasya:

    What backend data source are you using? Have you added correct SSO Id or other authentication information? Are you publishing the correct workbook? (Note that by default original QT is moved to a new workbook, don’t to publish that). How big is your QueryTable?

    Also, if you are doing everything right, can you elaborate more what exactly are the steps you are following? Also, what is the full error alert you are seeing?

  13. Sasya says:

    The Backend data source that I am using is MS Sql server. Authentication is Windows.I am sure that I am publishing correct workbook.

    Steps:

    1. Under the Data section connected to SQL server and selcted the required table.

    2. Under Add-In section, selected Convert to Refreshable QueryTable.

    3.In that selcted the option to generate to a new sheet.

    4.Publishing the Excel sheet to sharepoint Excel Services.

    and I get the following message in the browser.

    "Excel Services can not load the workbook that you have requested"

    I am getting the above error only when my table has more rows(3500).

    I have tried with some other table which has only 200 rows and it is working fine.

    Is there is any limitation for this add in to workbased on the size of the query table?

    Regards,

    Sasya

  14. Abdullah says:

    Just figured out that this occurs because of the large file size. By default, Excel Services allows loading a file whose size <= 10MB on the server. When you add a QT with >= 3500 records, the file size grows > 10 MB and following alert appears: "Excel Services cannot load the workbook that you requested." when you try to load that on the server.

    What you need to do is go to Central Admin Page | Select the SSP link on the left (below Shared Services Administration) | Click on "trusted file locations"

    Select the trusted location where your wb is, and set Maximum Workbook Size = 100 MB (also set "session time out" and "Maximum request duration" = 3000 or more to make sure that server has enough time to process the workbook during load and before time outs).

  15. Sasya says:

    Thanks for the quick response.

    I will try this out with my data.

    I have another question related to excel reports which is not related to this Add-in.

    I have excel reports uploaded to ReportCenter in MOSS, which are using odc file for getting data from SQL server.I am using Excel web access webpart to display the charts from the uploaded excel sheets.

    But here the problem that I am facing is that the data in database is getting updated daily. But the report that is shown in the Sharepoint Excel webaccess webpart is still old data.

    Is there a way to refresh report in Reportcenter automatically, without the user doing the refresh explicitly from browser.

    My requirement is that the report that is shown to the user at any point of time should be in sync with the data in database.

    Is there a way to automatically refresh this reports in the reportcenter.If no Can this be possible by implementing custom code.

    Thanks In Advance,

    Sasya

  16. Shahar Prish says:

    You can set individual connections to "Refresh on open".

    can do this from the Connections dialog.

  17. Sasya says:

    "Refresh on open" option is for excel sheet, and it is refreshing the report when the excel sheet is opened in Excel 2007. But I want the Report to be updated in Sharepoint. Is there any option to enable the automatic data refresh to the excel sheet that is being published to Excel services in sharepoint.

  18. Shahar Prish says:

    That option should affect Excel Services as well – if it’s not it’s either a bug, or something that’s being done wrong.

    Can you check and make sure that it’s indeed not working?

  19. Sasya says:

    Hi,

     Is there a way to refresh the data stored in Sharepoint Trusted locations, automatically without the user opening the workbook.I want the excel reports in sharepoint need to be in sync with the underlying database.

      Is there any setting, with which this can be achieved?

      I have tried with the Periodic Data Refresh option, but no use. I didt see the reports in sharepoint updated based on database changes. Can we do this refresh programatically by using Excel Services?

    Any help will be highly appreciated.

    Regards,

    Sasya              

  20. Shahar Prish says:

    there is no out of box feature that will allow you to do this. however, this is the second time in as many days that I am asked about it. I will post some sample code that will allow you to do this in the next couple of days.

  21. robman says:

    Hi did you have some news about the question from Sasya?

    "Is there a way to refresh the data stored in Sharepoint Trusted locations, automatically without the user opening the workbook.I want the excel reports in sharepoint need to be in sync with the underlying database."

    I am interested also on this.

    Thanks

  22. Shahar Prish says:

    There is no built-in way of doing that. You would need to write a timer job that would do that.

  23. bhakti says:

    hello sir

    now i’m developing macro for publish the excel sheet directly to sharepoint site.

    but till now i’m fail.

    if you have the code for that please send me or give me link or example.

    so i can do this thing.

    Thanks

    Bhakti chouksey

    INDIA

  24. Shahar Prish says:

    What is it exactly you are trying to do? To publish a sheet, all you need to do is save into SharePoint.

  25. itaysk says:

    Update: I have added some links to tools that should help you overcome this limitation. We were told

  26. simha says:

    Your tool is working great. It is converting the table to a refreshable query table. But when I public this table in a share point server it throws the error that "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:

    External data ranges (also called query tables)

    I removed the existing workbook,and published  the new one. But still it gives the same error

    Pls help in publishing this refreshable query table to share point. I need to refresh this table through excel services. How can we resolve this error.

    I have been working on this for so long time

    Help me

  27. Shahar Prish says:

    You need to make sure you remove the data-connection. You can use the compatibility tool to help you remove all these:

    http://blogs.msdn.com/cumgranosalis/pages/excel-services-compatibility-checker-download-page.aspx

  28. Sendil says:

    The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:

    External data ranges (also called query tables)

    Contact the workbook author.

  29. ak says:

    I have an issue with number of fields.

    If I have more than 11 fields, when I run this tool, the refreshable table is empty.

    If I have less than or equal 11 fields it works as expected.

  30. Ashik CK says:

    When using this i am getting error following

    The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:

    PivotTable reports that are set to refresh when the item in a report filter changes

    Contact the workbook author.

    any suggestion appreciated.

  31. Glenn says:

    I am getting the impression that the excel services in sharepoint are limited as far as data access are concerned.  Is there a solution for accessing data in excel either a backend database or a BDC list.  I am tring to use excel rather than write a custom web part to application.  

    Thanks

  32. ASHLEY_E says:

    I am coming accross an error on my QT after the conversion. Data is being populated into cells that shouldn’t have any data at all. I have checked the source file and it is correctly displaying only data where it should and before I complete the conversion the QT shows accurate data. I am only using the the default options for the conversion. Also when the Source QT is copied to a new workbook, the Master Wrkbook is linked to the copy. What am I missing or not doing that is causing these issues??? Help please.

  33. KP says:

    I did convert my table using the addin and did make sure to save the original connection in separate workbook and did remove the connection from existing workbook. However, when I try to upload the workbook using excel services, I get an error message

    " The workbook that you selected cannot be opened.

    The workbook may be in an unsupported file format, or it may be corrupt.

    Would you like to try and open this file in Excel? "

    Can you please help me with this error ?

  34. Shahar Prish says:

    What data is the query table bound to?