Automate Power Pivot excel refresh RRS feed

  • Question

  • Hi All,

    I would like to automate power pivot excel refresh. Please suggest if we can refresh it via SQL Agent job. Kindly share if any document or link it available.

    Or suggest other ways of automating this like using Powershell etc..



    Tuesday, September 27, 2016 11:21 AM


All replies

  • Hi Jim,

    You can do this with VBA: http://www.tomgleeson.ie/posts/201404/PowerPivotVBARefresh.html Please be careful as this is unsupported and can damage your PowerPivot workbook.

    Better and supported option is to use the Power Pivot Data Refresh with SharePoint 2013 https://msdn.microsoft.com/en-us/library/jj879294.aspx

    Jorg Klein's Microsoft Business Intelligence Blog

    Tuesday, September 27, 2016 12:11 PM
  • Hi Jorg,

    The power pivot work book is little heavy so DBA suggested not to use Data refresh through share point.

    Regarding VBA, I would prefer something which is centralized and not local to my machine like SQL agent, etc..



    Tuesday, September 27, 2016 1:26 PM
  • Hello Jim,

    Power Pivot for Excel is a client application, therefore you can update the Power Pivot data currently only within the client application = MS Excel.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 27, 2016 4:20 PM
  • Hi Jim,

    If I were you I would suggest to the DBA to try the data refresh in SharePoint and let him/her monitor the impact of the server. You can schedule the refresh at night outside office hours to minimize the impact.

    Jorg Klein's Microsoft Business Intelligence Blog

    Wednesday, September 28, 2016 9:06 AM
  • The challenge with not being able to automate power pivots or having a sanctioned Microsoft app to do that besides SharePoint is that the DBA drives the direction , but the business still inherently wants to be able to automate and save down Power Pivots to the regular server so there is not a need to pay someone to push refresh buttons all day long. In the time that has passed has another solution come up to your knowledge that allows this to occur, especially since with Office 365 it appears that Power Query has completely replaced the old ODBC query tool built into Excel; where I have tried , but can't seem to find a way override the SQL on the Power Query connection to be the actual SQL against the database source and its connection type. So, this way at least there would be a connection type and SQL where one could pass creds down via VBA to automate it. I know at least where I work it appears they weren't able to get SharePoint working and/or a decision was made not to use it for the automation component. However, the need for automation didn't just go away and we are constantly looking for different and better ways to automate Power Pivots or even Microsoft Access assets. Long term many things could probably be migrated to a cube based solution, however, this seems to be on a slower path than the speed for the business to model a new report based upon information needs at the time. Also, not all things can migrate - especially things where the business requires history to be kept. Could something like MS flow work - albeit at a cursory glance that looks mostly like a workflow tool. Any other option besides SharePoint for Power Pivot refreshes available at this point? Any plans at Microsoft to create something other than Power Pivot to make that happen?

    Thursday, September 20, 2018 5:12 PM