locked
Excel Service cannot refresh the data in excel. RRS feed

  • 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 list form.

    I created some chart in the excel based on the data. Then I uploaded this excel file to my SharePoint site, 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.

    Anyone knows what's wrong with it? Thanks a lot.

    Friday, June 17, 2011 1:24 AM

Answers

  • I find the solution.

    My excel is consuming the data from a SharePoint List. The data provider is "Microsoft.Office.List.OLEDB.2.0". In the default Trusted Data Provider of the excel service configuration. I found there is no one match the data provider I used. So, I added one. Then it works.

    • Marked as answer by Neil Yan Monday, June 20, 2011 4:53 AM
    Monday, June 20, 2011 3:23 AM
  • Hi Neil,

    The behavior you described is by design.
    The "Export To Excel" uses Web Acess Query(IQY) to query a SharePoint list. However, IQY only supports local Excel file. If we upload the file to SharePoint library, the related connection file(a local .iqy) cannot be found, and the following error message will be shown:

    The following features are not supported in the browser and might not display or might display only partially:

    • External data ranges (also called query tables)

    Some features, such as external data queries, display cached data which can only be refreshed in the client version of Excel.

    From your description, it seems you are going to create a chart based on the data from a SharePoint list. If I understand correctly, you can use the SQL Server Reporting Services(SSRS) to query the SharePoint list, and deploy the SSRS report to SharePoint library to accomplish the goal.

    For more information about SSRS SharePoint integrated mode, please see:
    Overview of Reporting Services and SharePoint Technology Integration: http://msdn.microsoft.com/en-us/library/bb326358.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jinchun Chen


    Jin Chen - MSFT
    • Marked as answer by Neil Yan Monday, June 20, 2011 4:53 AM
    Monday, June 20, 2011 3:24 AM

All replies

  • Neil,  can you clarify which SharePoint version you are working with and what service applications you have running?  Thanks.
    Friday, June 17, 2011 2:21 AM
  • I am using SharePoint 2010 and The excel version is also 2010.
    Friday, June 17, 2011 2:30 AM
  • I still not found the solution. No suggestions here as well?
    Monday, June 20, 2011 1:25 AM
  • I find the solution.

    My excel is consuming the data from a SharePoint List. The data provider is "Microsoft.Office.List.OLEDB.2.0". In the default Trusted Data Provider of the excel service configuration. I found there is no one match the data provider I used. So, I added one. Then it works.

    • Marked as answer by Neil Yan Monday, June 20, 2011 4:53 AM
    Monday, June 20, 2011 3:23 AM
  • Hi Neil,

    The behavior you described is by design.
    The "Export To Excel" uses Web Acess Query(IQY) to query a SharePoint list. However, IQY only supports local Excel file. If we upload the file to SharePoint library, the related connection file(a local .iqy) cannot be found, and the following error message will be shown:

    The following features are not supported in the browser and might not display or might display only partially:

    • External data ranges (also called query tables)

    Some features, such as external data queries, display cached data which can only be refreshed in the client version of Excel.

    From your description, it seems you are going to create a chart based on the data from a SharePoint list. If I understand correctly, you can use the SQL Server Reporting Services(SSRS) to query the SharePoint list, and deploy the SSRS report to SharePoint library to accomplish the goal.

    For more information about SSRS SharePoint integrated mode, please see:
    Overview of Reporting Services and SharePoint Technology Integration: http://msdn.microsoft.com/en-us/library/bb326358.aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jinchun Chen


    Jin Chen - MSFT
    • Marked as answer by Neil Yan Monday, June 20, 2011 4:53 AM
    Monday, June 20, 2011 3:24 AM
  • Hi Jinchun,

    Thanks for your suggestion. I found the solution the make the excel web access webpart refresh the data in my excel file even the data is from my SharePoint list.

    The issue is that the data provider for sharepoint list is not trusted by default. It works after I added "Microsoft.Office.List.OLEDB.2.0" as a trusted data provider.

    So, I think the IQY is also embed in the excel file if I don't explicitly assign an external data source file.

    Thank you anyway. You give me some insights about another powerful implementation method.

     

    Thanks,
    Neil

    Monday, June 20, 2011 4:57 AM
  • Hi Neil,

    I have the same issue as you. So could you please let me know where you have added "Microsoft.Office.list.OLEDB.2.0" as a trusted data provider?

    I also haev SharePoint & Excel 2010.

     

    Thanks a lot

    Regards,

    Géraldine

    Tuesday, August 2, 2011 12:10 PM
  • I have an article that will show you how to use an Excel Services UDF to render realtime SharePoint list data inside of Excel Services pivot tables and charts.

    see here for details


    Please mark my response as an answer if appropriate.
    Learn.SharePoint.com

    Monday, August 26, 2013 10:16 PM
  • Hi Neil,

    I have the same problem as you mentioned above. I have added "Microsoft.Office.List.OLEDB.2.0" as a trusted data provider in the Central Administration. Still I'm facing the same issue.

    Can you please help me on this?

    Wednesday, May 21, 2014 6:12 PM
  • I don't think so... Why forum support always provides such unverified answer...
    Saturday, July 15, 2017 3:55 AM