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
Yes there is an option to refresh the excel file on open and set credentials as well.
Here are a few screenshots to better aid you: