Using UDFs and Excel Formulas to Get a Query Table on Excel Services
As you may or may not know by now, one of the limitations in this version of Excel Services is the inability to publish workbooks containing Query Tables (tables that have external data as their source). There have been numerous blog posts on the web about how to covnert your QueryTable into a PivotTable to get this to work, unfortunately you loose some QueryTable functionality doing this, and you have to run some custom code against your workbooks (check out https://blogs.msdn.com/cumgranosalis for some add-ins that will do this for you).
I have been using a different workaround to achieve this same functionality, it's simple, works well with my UDF examples, and doesn't require any code (though you probably wouldn't want to convert large workbooks you have laying around by hand, but this may help with new ones).
As I mentioned in this post you can have your UDFs return an array to the Excel workbook by entering them as an array formula. Once the data is in the workbook you will notice that structure-wise it looks very much like a Query Table, though you cannot sort/filter on it, you also can't simply create a table containing that range as tables cannot be created against array formulas.
WORKAROUND
The workaround is simple, instead of creating a table containing the array formula returned from the UDF, we will create a table on a separate sheet which references the array formula's cells individually.
I've attached a workbook that does this, but in order for you to see what I'm doing on Excel client I have used the "=ROW()*COLUMN()" formula instead of an UDF which would evaluate to #NAME? on the client until you registered the dll... etc...
The workbook contains an array formula in Sheet1 range A1:D7 with the formula "=ROW()*COLUMN()" entered with CTRL+SHFT+ENTER.
In Sheet2 you have a table from A1:E8 (one extra row for the column titles, one extra column for indexes). You will notice a special INDEX column to the right of the table, this is filled as 1, 2, 3,..., 6, 7 and corresponds to the row in Sheet1 where the data for the current row in the table is located.
Every other cell in the table (minus the INDEX column) contains the following formula:
=INDIRECT("Sheet1!R" & Table1[[#This Row],[INDEX]] & "C" & COLUMN(), FALSE)
Basically what this is doing is creating an absolute reference to the cell on Sheet1, and grabbing it's value. You would actually also be able to do this by filling your table with absolute references like =$A$1, =$A$2,... etc, but there is no way that I know off (and I asked around quite a bit) to be able to enter those references automatically in a large range short of writing some code which I want to avoid.
Either way, you should now see the values from the array formula on Sheet1 in your table on Sheet2 (plus the INDEX column on the right which you can hide).
You can now Sort, Filter, and do anything you can do on a good old Query Table. If instead of using the simple "=ROW()*COLUMN()" function you used a UDF that reads from a SQL database and returns an array (or you could also use my UDF to read from SharePoint Lists) then you end up with an almost fully functional table of the external data.
THE CATCH
There is always a catch... the one that seems to be prevalent here is the fact that you need to define right off the bat what your data dimensions will be when you enter the array formula, so if somehow your data grows bigger than the size you allocated for the array formula the table will not automatically grow to that size. The workaround here is to make sure your array formula is big enough for your data to grow (pad it with some extra rows) and filter out any blank rows on the table so they don't show up until they have data on them.
See sample workbook attached!
Comments
- Anonymous
January 09, 2007
As you have noticed, many of my samples below use an Array Formula in Excel to retrieve an array of data