Tell me more ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.
up vote 0 down vote favorite

I have created a Excel and enabled the connection to external sQL database through Secure store service and created the odc file . When i open the excel in sharepoint it refreshes the data and it is working. When i click "Open in Excel" the data is downloaded and opened in Office excel 2010. When i refresh the data it prompts me for a password to be entered. Please advise me if we have any settings which needs to enabled for the data refreshing when we open in excel

share|improve this question

1 Answer

up vote 0 down vote

Yes there is an option to refresh the excel file on open and set credentials as well.

  1. Open up your excel file and go to Data>>Connections.

  2. Find your connection and click on properties.

  3. Under the Usage tab (which is what is shown by default)Select the checkbox "Refresh Data when opening this file". You could even use "Refresh data every:" if you wish to take that route

  4. Click on the "Definition" tab on top (adjacent to Usage tab) and click on authentication settings at the bottom.

  5. You have 3 choices here. Use the one that suits your/business needs. Make sure whatever account you use here has the proper access to the data. Since you already have a secure store service set up use that SSID here (as shown in the screenshot). Please note that your SSID might be named differently.

Here are a few screenshots to better aid you:

enter image description here

enter image description here

share|improve this answer
Thank You . Do we have option to manually click and refresh the data – Jagadeesh Jan 23 at 9:47
I have done the same authentication settings using the secure store key where i have my username and password. I have enabled the settings for refresh as shown above but after every 15 mins, I get a popup screen which prompts for password – Jagadeesh Jan 23 at 10:11
Manual Refresh - Yes you can do this on both the Excel Web App in Sharepoint (Click on Data>>Refresh All Connections) and the excel workbook itself (Data>>Refresh All). Credential Prompt Issue - Try checking the "Save Passowrd" checkbox under the connection string in your connection string properties>>Definition – user7400 Jan 23 at 15:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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