Excel Services data refresh does not return updated data from data source

Question

I have an Excel include some data which from a SharePoint list. This excel is created by using "Export to Excel" button of the SharePoint list form. I created a pivot table in the excel based on the data. Then I uploaded this excel file to my SharePoint site library, and show it in an Excel Web Access webpart.

When I add a new item in the data source list. Then I go to excel web access web part, try to refresh selected/all connections. The new item was not synced into the excel. If I open this excel by using desktop application. The refresh connections works well.

I have tried to set the connection properties "Refresh data when opening the file" and "Enable background refresh" to yes. Also it doesn't work on excel web access webpart.

I have also added "Microsoft.Office.List.OLEDB.2.0" as a Trusted Data Provider of the excel service configuration in the Central administration as per the MSDN forum http://social.technet.microsoft.com/Forums/en-US/5e24e62b-073e-4794-a3d5-3210f351d206/excel-service-cannot-refresh-the-data-in-excel

I have also set External Data Cache Lifetime in Central Administation "Automatic refresh (periodic / on-open)" and "Manual refresh" number from 300 to 50. Still I don't see my entry.

Still I am facing the same issue. Can anyone help me please?

Answer 1

Actually you can't refresh the data by this way. Export to excel option was not supported by excel service. You need use the odata from sharepoint list. http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/07/11/excel-services-using-a-sharepoint-list-as-a-data-source.aspx

Answer 2

Create a Data Source document library in your Sharepoint site and upload the iqy file there. Check it in and approve it. Rebuild your workbook to use that datasource. Publish via Excel Services.