SharePoint Products TechCenter >
SharePoint Products and Technologies Forums
>
SharePoint - Excel Services
>
Open in Excel not displaying UDF values
Open in Excel not displaying UDF values
- I have a workbook with a few sheets for a table, chart and source data. The source data is populated from SharePoint lists using a UDF array function.
This all works fine.
However when I dowload the workbook using the "Open in Excel" option any cells that were populated via UDF show #VALUE. This suggests that the Excel client can't find the UDF which I can understand as they are on the server. However I would expect the actual values to shown until attempting to recalculate the workbook. The workbook is set to calculate automatically and this needs to be on for the book to work in excel services correctly.
The open in snapshot option works fine.
Any Ideas??
Zac Smith My Blog Twitter
All Replies
- Is this an Excel Services question ? (Function of MOSS Enterprise edition)It seems to me like a question about uploading an Excel file to a SharePoint Doc Lib and then opening it with Open in Excel and therefore a standard SP Admin question that has nothing to do with Excel Services.
WSS FAQ sites: http://wssv2faq.mindsharp.com and http://wssv3faq.mindsharp.com
Total list of WSS 3.0 / MOSS 2007 Books (including foreign language) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx - Yes this is an excel services question. Creating a UDF is hardly an admin function, and this is obviously a big part of the question as I included that in the title of the post.
One thing that I did not make clear -> I am using the Excel Web Access web part to display the workbook, and there are several web parts with connections that provide parameters for the UDF.
Zac Smith
My Blog
Twitter - Ah, if I'd seen Excel Web Access mentioned I wouldn't have asked the question I did.(Missed the UDF / Excel Services connection - I thought it was an Excel thing I'd never heard of... Apologies, especially considering I'm supposedly no longer moderating - old habits die hard.)
WSS FAQ sites: http://wssv2faq.mindsharp.com and http://wssv3faq.mindsharp.com
Total list of WSS 3.0 / MOSS 2007 Books (including foreign language) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx - I have stripped this right back now so that there is:
- A workbook with one worksheet
- The worksheet has a UDF formula in the first cell - LoadTestData()
- a UDF is created named LoadTestData() and it returns a test string value
- Using EWA I can load the workbook and see that the test value is displayed in the web part
- Opening in excel client shows the worksheet cell as #NAME
- Open in snapshot works no problem.
Now I think this should work, quoting from Microsoft Online, the open in Excel option does the following:
"The current state of the entire workbook including any interactions you have made in the current session, such as sorting and filtering, and including recalculated formulas and refreshed data connections, is copied to your local computer and opened in Excel so that you can view and change it."
Is this a limitation of Excel Services or is there something additional that I need to configure?
Zac Smith
My Blog
Twitter I did a PoC a while back using Excel Services and observed this behavior. The UDF is likely defined in a DLL that is deployed on the SharePoint server and once the file is opened locally, the "current state" of the workbook is transferred. Of course the cell still has the UDF reference so it tries to find the function so that it can pull the data. Since the function is likely not installed on the client machine, the function fails which results in the #VALUE error.
I found this to be a limitation of using UDFs and abandoned the approach.
Dan Luciano - Sr. Consultant MCP, MCTS WSS 3.0 and MOSS 2007 Configuration WSS 3.0 Applicaton Development
