Query Tables work-around for Excel Services

Both in the internal distribution lists and on my blog, the two most insistent question we seem to be getting these days seem to be:

1. My workbook has a Query Table (relational data source in the form of a table) in it and it doesn’t load on the server. What gives?

2. What? You REALLY don’t support Query Tables?

So the answer to (1) is that that no, we don’t support Query Tables with this version. The answer to (2) is that no, we really do not support them.

With that said, let me present Boaz-I’d-rather-be-photographing-Chen who has been with the team since the first day of Excel Services. He wrote an Add-in that takes a Query Table and turns it into a PivotTable that looks and behaves almost the same.

In his words:

This is a little add-in to convert a query table to a pivot table I wrote after getting repeated feedback from customers about not being able to load their workbooks which contain query tables (aka External Ranges) on the server. Mostly we reply that they need to replace their query table with a pivot table, and then there is always the question of how to do it.

The add-in manages to give pretty nice results, and the auto created pivot table looks very similar in its layout to the original query table.


· Basically the same operations (Filter, Sort, Totals) are supported on the pivot table.

· When trying to sort the auto created pivot, only the left most column can be sorted. The reason is that the fields are kept in a hierarchy inside the pivot, and there is no option (As far as I know) to break this hierarchy when wanting to sort on a non top level field.

· The add-in optionally removes the original query table and its workbook connection at the end of the conversion (You will get a dialog to confirm this).


1. Save the attached xlam anywhere on your drive.

2. In Excel, Office button | Excel Options | Add-ins

3. Down at the bottom: Hit the Go… button

4. Browse to the saved add-in.

5. OK everything and reboot Excel. There will be an Add-in menu added to the ribbon with a Custom Toolbars chunk. The conversion button should appear there.


Comments (10)

  1. ts46235 says:

    I am trying to use this and i get the following error: run-time error: ‘445’ object doesn’t support this action.

    It blows up on this line:

    Set pivotConnection = ActiveWorkbook.Connections.Add _

           (tableConnection.Name, tableConnection.Description, tableConnection.OLEDBConnection.Connection, _

           tableConnection.OLEDBConnection.CommandText, tableConnection.OLEDBConnection.CommandType)

    I am working on debugging the issue. All of the parameters for the add method have seemingly correct values except tableConnection.Description is blank.

    I’ll keep troubleshooting

  2. ts46235 says:

    OK, I figured out the problem. The addin uses OLEDB connections (as seen above in previous post). I created my data connection (.odc) by using the data connection wizard in excel and when i opened it up in notepad it’s odc:Type="ODBC". Seeing this, I updated the code to be ODBC connections:

       Set pivotConnection = ActiveWorkbook.Connections.Add _

           (tableConnection.Name, tableConnection.Description, tableConnection.ODBCConnection.Connection, _

           tableConnection.ODBCConnection.CommandText, tableConnection.ODBCConnection.CommandType)

    This seems to work and i can open it thru excel services!

    You could probably modify your .odc file’s connection string to be an OLEDB type and keep add in as is.

    thanks Shahar

  3. Alan says:


    I’m new to this and somewhat frustrated.  How can I create an Xxcel file that will load its data from SQL for use on a page with Excel services?  Is there a step by step set of instruction anywhere that makes no assumptions about the end users level of knowledge?  I’m really in a bind here the best I can get is a query tables rejection.

  4. Shahar Prish says:


    SQL tables are not supported in Excel Services as they are in Excel.

    One of the workarounds is to use the add-in in this post to do it. The instructions are in the post. If you have any questions specific to that, feel free to ask them.

  5. Padmaja says:

    Is there any source code for this workaround available?

  6. Shahar Prish says:

    Yes, if you download it, you will be able to access the VBA code in it.

  7. markovich says:

    SQL tables are not supported in Excel Services as they are in Excel.

  8. itaysk says:

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

  9. En mi experiencia, he visto que este es uno de los temas poco desarrollados por el momento en los portales

  10. pnoble says:

    is there an updated version of the add-in?  I followed the instructions and when i open up excel i receive the following:  This workbook was created in an earlier beta version of Excel 2007.  Excel will convert the workbook to the most recent version of the Excel 2007 file format when you save it.  Before opening this workbook, verifty that it is from a trusted source.

    Will i get this message everytime i open up Excel?