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
In
the Excel Services Options dialog’s Show tab, select Items In The Workbook from
the drop-down list.
Check
the All Named Ranges and the All Charts options to ensure that they will be
available for use by the EWA web part
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.
