Before we start troubleshooting on this, let’s try to understand
the term-
What is meant by name
item? - In this view, you can only display one
or more named items in the workbook. A named item can be one of the following:
1)
A chart
2)
An Excel table
3)
A PivotTable report
4)
A PivotChart report
5)
Named range of
cells.
How I encountered
this error? – I have added an excel web access web part on my SharePoint
site collection, modify the webpart and entered the details for workbook
location and name item field by specifying tab7 inside the excel spreadsheet as
I don’t want to display my spreadsheet in the EWA.
As soon as I clicked ok, the excel web access webpart
throws an exception as follows:
The named item cannot be
displayed. The name item is either not available in the workbook or was not
selected as a name item for viewing when the workbook was published. The name
item may also be located in a hidden range of cells or in a collapsed outline.
Action taken /
troubleshooting done:
1.
Checked the status of excel service application
and it was fine
2.
Checked the excel service status by means of
Central Administration- services on server and it was fine too.
3.
Need to isolate the problem first- whether its
farm specific or file specific or EWA web part specific.
4.
Checked the document library where all the excel
documents were stored. Clicked on the dropdown of any excel file and selected ‘view
in browser’ – it opens up fine!
5.
Came back to the excel web access webpart-
modify it and remove the name item field and kept it blank – click apply &
checked the results
6.
With the above action- the excel web access web
part loads the excel file without any problems.
7.
So far so good! Still trying to find out the
resolution!
Resolution:-
Change Published
Items within a Workbook: -
When we initially published our workbook to Excel
Services, we simply gave it a name and accepted the default values. Whenever we
click the Save icon, rather than re-publishing the workbook, we’re merely
saving the data back to the document library. The significance here is that
when publishing a workbook to Excel Services, we have the option of specifying
additional metadata, but when saving, the metadata is not changed. We received
the error because the metadata did not contain details of our new named item.
1.
Open the excel file on your desktop (not from
the SharePoint site, if you don’t have saved on the desktop then please save
it.)
2.
On the top ribbon- click on File
3.
Click on Share
4.
Publish to Excel Services
5.
Current location
6.
Save as - automatically displays the contents of
our Excel Workbooks document library
7.
Click Publish to Excel services to open the save
as dialogue
8.
Excel Services Options button now appears
in the bottom
9.
Excel Service Options button
10.
Here we need to define or overwrite metadata for the document
11.
In the Excel Services Options dialog’s Show tab,
select Items In The Workbook from the drop-down list.
12.
Check the All Named Ranges and the All Charts
options to ensure that they will be available for use by the EWA web part
13.
Click Save to complete the publishing process
That’s it- You are done!
Make the necessary changes inside the excel web access (EWA)
and check the results.
If you have any queries/questions regarding the above
mentioned information then please let me know. I would be more than happy to
help you as well as resolves your issues, Thank you.
