Strange behaviour when opening Excel files from SharePoint

Question

When one of my users opens a SharePoint stored Excel file containing data connections Excel seems to ask for their O365 credentials. The user enters their credentials (despite being signed in to Excel) but their credentials are not accepted.

When investigating I noticed that after selecting 'Enable Content' the O365 window opens but the URL points to our SharePoint tenant and the rest of the path points to a Shared Drive location! Of course, after entering your O365 credentials they are not accepted because the full URL does not exist...

Has anyone come across this before? Can this be resolved?

Answer 1

Hi Zixuan,

Thank you for your detailed response.

1. I can confirm I also experience the issue but only when using the problematic workbook. 
2. The data connection is a simple lookup to another Excel workbook. It was added using the 'Paste  Special' > 'Paste Link' functionality.
3. The external workbook is stored on a Windows File Share, I checked the permissions and can confirm that the user has permissions. 
4. You can find a screenshot in the above threat, the screenshot is titled 'Sign in'. The URL starts with our tenant name (i.e. https://test.sharepoint.com), after the tenant URL it contains the path of a Windows File Share. I'm not sure where this is coming from because when selecting 'Data' tab > 'Edit Links' in Excel the location is correct and does not reference anything SharePoint related. The only thing SharePoint related is the fact the workbook is stored within SharePoint...

I just tried replicating this using a two new workbooks, test1 (populated) and test2 (blank).

I copied data from test1 in to test2 and can confirm that the data within test2 is updated when I update the cells within test1. I also tried uploading test2 to SharePoint and editing in the Excel client and can confirm that I do not experience any issues.

I am quite confident that the issue only occurs on the Excel workbook that the end user is using. Do you have any suggestions of what to check?

Many thanks,

Callum

Answer 2

Hi Callum,

Thanks for posting in our forum. We will try our best to provide support for you.

I tried but not repro the behavior you encountered. The following are my test and results:

I created two simple workbooks(obworkbook.xlsx and rbworkbook.xlsx ) to test. First, I create them locally. In obworkbook.xlsx, I selected a cell, and use another cell in rbworkbook.xlsx as its external reference. After that, I uploaded these two workbooks to a document library in SharePoint. I got the Enable Content notification as well when I tried to open obworkbook.xlsx and the link will update to refer to SharePoint location automatically. And it won't ask me to sign in again after I clicked Enable Content. Additionally, after I changed the value in the reference cell in rbworkbook.xlsx in SharePoint, the next time I opened obworkbook.xlsx, it will pop up another notification to ask me whether update the cell contains external link.

To better investigate the behavior you encountered, could you please share the following information:

1. Does this behavior only occur with that user you mentioned?
2. Could you please describe the details of the "data connection"?
3. Does that user have permission to access the external source workbook?
4. Regarding to "their credentials are not accepted.", please capture a screenshot. Please only mark your tenant in the link, we would like to know what you mean by "Shared Drive location".

Regards,

Zixuan

Answer 3

***More Information***

1. Open workbook and the below is displayed. 

2. Check Edit Links location. Points to the right location.

3. After selecting 'Enable Content' the O365 sign in prompt appears. The URL is a combination of SharePoint and the Shared Drive...