locked
Script for update powerpivot Excel File RRS feed

  • Question

  • I would want to know if there is a solution (program, script, macro, etc...) to update powerpivot data for Excel automatically (once a day for example, without user action).
    Do you know if it is possible?

    Thanks.
    Wednesday, December 8, 2010 12:06 PM

Answers

  • Hi nico50362,

    In order for us to update PowerPivot data for Excel automatically without any user's interaction, we can develop a program that calls the Microsoft.Office.Interop.Excel
    object to refresh the Excel.
    We can use the QueryTable.Refresh update the external data range.

    For more information about QueryTable.Refresh, please see:
    http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._querytable.refresh.aspx

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

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by Challen Fu Friday, December 17, 2010 9:11 AM
    Tuesday, December 14, 2010 2:37 AM

All replies

  • Hi nico50362,

    In order for us to update PowerPivot data for Excel automatically without any user's interaction, we can develop a program that calls the Microsoft.Office.Interop.Excel
    object to refresh the Excel.
    We can use the QueryTable.Refresh update the external data range.

    For more information about QueryTable.Refresh, please see:
    http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._querytable.refresh.aspx

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

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by Challen Fu Friday, December 17, 2010 9:11 AM
    Tuesday, December 14, 2010 2:37 AM
  • Hi,

    No, there is no way in this version of PowerPivot for Excel to update the source data programatically as there is no API for PowerPivot.

    You need SharePoint 2010 to be able to manage and schedule the PowerPivot data refresh.

    Regards,

    Hrvoje Piasevoli

     

    Tuesday, December 14, 2010 5:32 PM
  • Can you update a normal pivottable in Sharepoint 2010? or do you have to have a PowerPivot?

     

    Thank you,

    Andy

    Wednesday, December 22, 2010 3:43 PM