Connecting to an Excel file on Sharepoint - password Missing/Invalid prompt

Question

Hello,

I am dealing with two connected Excel files.  One is a data list stored on SharePoint.  The other file is a simple dashboard connected to the data source file via the Data, Existing Connections command.  The dashboard is real simple and contains a simple PivotTable connected to the data in the other file.    The files need to be separate files since the target audience should not see the underlying data;  they should only see the pivotTable on the Dashboard.

In a test, the Excel dashboard file was sent as an attachment in an email to someone else.  If that person downloads the dashboard from the email and onto their desktop, they get the dialogue box upon opening the file: (see picture 1 below).  Fortunately, if one clicks the Cancel button the person , you can still access the data file on SharePoint, create PivotTables that utilize it, and slicers as well.

Here's where I need help: How can the recipient of the email avoid getting picture 1 below about the password is missing or invalid?  In the Data Connection box which controls the connection between the dashboard and the data source (picture 2) “Save password” was checked in an effort to fix it, but the problem persisted.

Thank you for any assistance in this matter....Craig

Picture 1: 

Picture 2

Answer 1

Hi CraigCB,

I go through the post carefully, as you can see the link: Connection properties

“Save password    Select this check box to save the username and password in the connection file. The saved password is not encrypted. Clear this check box to log on to the data source, if a user name and password are required, the first time that you access it. This check box does not apply to data retrieved from a text file or a Web query.”

May I know did you select the box before you send it to other users in email attachment? As it seems that data source is encrypted , please select the box. typing right username/password here, then share it and check if users can refresh data without seeing the prompt. Additionally, may I know what kind of data the dashboard workbook connected?

Regards,

Clark

Answer 2

Hi, thanks very much for your assistance.

The password check box was not selected before sending to others via email attachment.   The data source was created in Excel.   I don't think the data source was corrupted.   I found out the data source was saved in a hidden system file location:  \application\local\microsoft\windows\inetcache\MSO\excel file.

Do you have any further suggestions?

thank you

Craig

Answer 3

Hi Craig,

Thanks for the update, I am sorry, I mean it seems that your data source is encrypted with password. The prompt in Picture 1 shows up when some data in the workbook connected to external source that is encrypted when you refresh data. if you don’t enter any password, even if you checked “Save password” check box in connection properties, it may not work as no password was saved. So you may try to type password on the users’ device once, or you may share the workbook with selecting “save password” and type password in it, then share the workbook with other users and check the result.

Regards,

Clark

Answer 4

Thank you very much, i think it has to do with encryption password and will look at that.