Wednesday, June 10, 2009

Excel Web Services format issue “Accounting” format vs “Currency”

Strange, but when you publish an Excel sheet to Sharepoint and view it with Excel Web Services, cells formatted as “Accounting” show as follows:

clip_image002

but when you change the format in Excel to “Currency”, it shows correctly:

clip_image004

Could it be some kind of spacing issue with the rendering of the “$” within the web page?

Tuesday, June 9, 2009

Fix - Excel Web Services returning "External References" Error

Today, we got a strange error when trying to open a published Excel 2007 Workbook in on Sharepoint with Excel Web Services.  The Excel web part returned an error saying "External References (links to other workbooks)" we in the file and could not be opened.  The 12 Hive log files had the same error and did not help any further with what the issue could be.

Looking at the file's external links properties in excel showed nothing.

I ended up renaming the xlsx file to a zip, and unzipping the Excel file… scanned the workbook.xml file and found a reference to an “external reference” that did not show up in the Excel interface:

image 

Searching for the unique id in the rest of the zip file, i found it lead to a “Named Range’ that pointed to an external file.

Voila, went back into Excel, removed the Named Range….

image

Republished the Excel file and it worked.

MS should update the sharepoint error message saying a formula, or named range is referencing an external file.  Would have helped.

Cheers

S.