1

I have a BI Center site, I'm trying to open an Excel sheet in the PivotView gallery but it gives me this error:

Workbook cannot be opened

I have uploaded the Excel sheet in another library but it still gives the same error message.

Also when I open the Excel sheet using client application it works fine.

How do I fix this?

UPDATE:

I'm using the same excel sheet again. I'm fetching data from an SP list using Odata feed connection; http://sharepointsite/_vti_bin/listdata.svc In the connection properties I set it to "None" so that it uses SSS

In central Admin > Application Management > Manage Service Application > Secure Store Service, I have created a new targetd application ID, members: domain users and set the credintials to an account that has access on the list data (has access on sharepoint site in general) then I upload the excel to sharepoint to open it in web view, I refresh the data connection to fetch new data but I get this error everytime:

External Data Refresh Failed: An error occurred while working on the Data Model in the workbook. Please try again.

We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh:

DataFeed connection_name listdata

ULS Logs:

External Data ajeso Information Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) '', has been specified but has not been used. The following system error occurred: Class not registered The provider 'Microsoft.Mashup.OleDb.1' is not registered. , ConnectionName: ThisWorkbookDataModel, Workbook: Book1.xlsx.

Another one:

Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) '', has been specified but has not been used. Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: The remote name could not be resolved: 'sharepointsite'. , ConnectionName: ThisWorkbookDataModel, Workbook: Book2.xlsx.

  • Just to be sure - are you using IE to open the Excel workbook? – moe Jan 7 '16 at 9:58
  • @moe yes I'm using IE – Khalifa Sabt Jan 7 '16 at 10:01
  • it's not an IE issue, as we have two sites running BI Center. on the other site I can view the excel sheet in the browser with no issues. I have enabled Excel Services in Service Application Associations - Central Admin for the faulty site but the issue is still persisting. – Khalifa Sabt Jan 7 '16 at 10:03
  • 1
    Have you added the necessary permissions for the service account running the Excel Services? See e.g. blogs.technet.com/b/… – moe Jan 7 '16 at 10:06
1

To promote the answer for others, as this was solved in the comment section of the question:

The The workbook cannot be opened -error is supposedly caused because the account running the Excel Services at Central Administration doesn't have the proper permissions to the Content Database.

The appropriate rights can be granted with the following commands:

$w = Get-SPWebApplication –Identity <URL of the Web application>
$w.GrantAccessToProcessIdentity("<domain\ServiceAccount>")

Source including the full explanation.

UPDATE:

This MSDN article suggests the issue is on the privileges of the service account running the Analysis Services (See the part "Windows Authentication with Workbook Data Connections and Interactive Data Refresh").

Assuming the environment is running on SQL 2012 (if not, change the SQL version to the one you have at the beginning of the article), briefly these are the steps to add the required privileges:

  1. On the Analysis Services Server running in SharePoint mode, Add the Analysis Services service account to the "Act as part of the operating system" privilege:
    1. Run secpol.msc
    2. Click "Local Security Policy", then click "Local policies", and then click "User rights assignment".
    3. Add the service account.
  2. Restart Excel Services and reboot the Analysis Services server.
  3. Delegation from the Excel Services service account or from Claims to Windows token service (C2WTS) to the Analysis services instance is not required. Therefore no configuration for KCD from Excel Services or C2WTS to PowerPivot AS service is necessary. If the backend data source is on the same server as the Analysis Services instance, Kerberos Constrained Delegation is not required. However, the Analysis Services service account requires the right to Act As Part Of The Operating System.
  • It opens the excel sheet in web view but when refreshing data connection I get an error occurred in the data model... I have tried configuring unattended account sss and gave proper credentials to it and edited the connection string in the excel sheet to use that account but still having the same issue – Khalifa Sabt Jan 11 '16 at 13:48
  • @KhalifaSabt could you update the original question with details about the error you receive. – moe Jan 11 '16 at 13:52
  • I have updated the question – Khalifa Sabt Jan 11 '16 at 14:00
  • @KhalifaSabt the answer has been updated. – moe Jan 12 '16 at 10:19
  • 1
    After struggling with it for a week... it turned out it was a permission issue on the sql server and on the SharePoint site. literally I gave contribute permission to the relevant services and pools accounts felt like a geeky opera winfrey for a moment, anyway, then i did a server refresh, along with the configurations that I have done in the past that I have no memory of, it worked! Thanks my friend for your help and support. Really appreciate it. – Khalifa Sabt Jan 13 '16 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.