Monday, March 20, 2017

Sharepoint documents will not open in Word/Excel

Sharepoint documents will not open in Word/Excel

Today I have been dealing with a very interesting Office 365 / SharePoint issue. It was reported that the Edit in Application button within SharePoint i.e. “Edit in Excel” “Edit in Word” is not working correctly and results in an empty Spreadsheet or empty Word document.

Viewing and Editing within the browser is fully functional and the behaviour varies depending on computers within specific OU in active directory. It is worth noting at this point the estate consisted of Office 2013 ProPlus installations running on Windows 8.1.

The fact that identical systems with identical software versions patch to the same level, resulted in different behaviour could only mean Group Policy was different between them.

I was quickly able to find the offending policy; "Block signing into Office: Enabled". The screen shot above details a systems policy with the issue.

With the Standard Workstation GPO's the policy "Block signing into Office: Enabled" is not configured and Office applications can see the Sign-In option and can login to Office 365.

Upon logging into a domain joined system Microsoft Office will login by default with the same account used to login to the computer. If the account used to login to the computer ( i.e. SA_U1234567) is different from the account used to login to Office 365 (i.e. U1234567) the application Excel will fail to authenticate. The Application will attempt to open the SharePoint URL intended for U1234567 and authenticate site/file; it will attempt to authenticate against the locally log on user ( i.e. SA_U1234567) who does not have permission and fail.

For example the SA_U1234567 will need to have permissions to the resource U1234567 has selected within the Office 365 portal to "Edit in Excel". If the account SA_U1234567 does not have rights Excel will present, the user with the following Information message. "Sorry, we couldn't open HTTPS://...SharePoint.com/Sites/…../Document.XLS"

It will follow up with a Warning message explaining:

Microsoft Excel cannot access the file 'HTTPS://...SharePoint.com/Sites/…../Document.XLS". There are several possible reasons:

The message clearly explains that excel cannot access the file. Without an authenticated account the file will not be accessible; the first “possible reasons”, “The File name or path does not exist” is true from the perspective that Excel cannot find the file request.

If the account used to login to the computer is U1234567 and Office 365 /SharePoint is authenticated with the account U1234567 then the “Edit in Excel” will pass the MS-Excel Protocol a site URL that can be authenticated; resulting in success.

The Workstation that was unable to “Edit in Excel” was applying the policy "Block signing into Office : Enabled". This configuration blocks the ability for the Microsoft Application to Sign-in; Excel was not able even attempting to authenticate against Office 365.

Within Office 365/SharePoint when the button is clicked to "Edit in Excel" essentially the user is initiating a hyperlink calling the protocol MS-Excel with the full URL to the resource.

ms-excel:ofv|u|https://....sharepoint.com/sites/......EXCELFILE.xlsx

In the case of the Workstation that was unable to “Edit in Excel” the protocol for ms-excel opens Excel (set through File Association) and the URL is ignored. As the Sign-In is block Excel does not even attempt to find the SharePoint site and the user simply see a blank Excel spreadsheet. It is unfortunate that the User does not get a dialogue box explaining the Sign-In is block and is left to believe the operating failed.

Conclusion

It is worth noting that If you are seeing the Information and Warning dialogue boxes your issue will most likely relate to Authentication and access to the resource. If you are presented with an empty Excel or Word document then Policy is a root of investigation.