Power Pivot not refreshing on opening. Excel 2016

SueBristow

New Member
Joined
Dec 15, 2017
Messages
4
I have set up three reports to generate overnight and create 3 .csv filesI have created a spreadsheet where these csv files are read by the data model and from there, create power pivot tables.The requirement is that the pivot tables read the most current data whenever they are opened - they are all protected but slicers are active as they are to be used by all members of the school.After much watching of you-tube and hours of googling I have found two places where ticks need to be added to update the data on opening. However, the data is still not updating when I open and I have to manually refresh (using a slicer also updates the data). This is fine for me, but as this spreadsheet is for all to use, it is important that it updates on opening.The two places I have found to update on opening are:On the Pivot table itself, right mouse click, Pivot Table Options, Data Tab, Refresh Data when opening the file - this screen appears to be the same as going via Analyze, Pivot Table OptionsThe other place is Data, Connections Properties and here you also get the chance to Refresh every 60 seconds as well as the Refresh when Opening - I've ticked both.Is anyone aware of any other places that refresh on opening should be set please?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
My recollection is that there isn't a setting to auto update the model's data source, but you could use code in the workbook_open event that does something like:

Code:
       With ThisWorkbook.Model
   .Initialize
   .Refresh
End With
 

SueBristow

New Member
Joined
Dec 15, 2017
Messages
4
Thanks - I was trying to get away from code on opening and it makes you wonder why the two places that I have found exist if they are not going to update the data.As a side issue - the formatting disappeared when I sent the first post making it difficult to read. How to I keep line breaks in there?
 

SueBristow

New Member
Joined
Dec 15, 2017
Messages
4
I have added the VBA and checked that it updates on opening the workbook but it is only updating the first sheet, not the ones behind. I just don't understand
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Perhaps add:

Code:
Thisworkbook.RefreshAll

to the end of the Workbook_Open routine to ensure all the pivots get updated after the model is refreshed.
 

SueBristow

New Member
Joined
Dec 15, 2017
Messages
4
I will try that code but have found what the issue was even though it is a little odd. Because the reports are for others to use, I had protected them but opened up the slicers to allow the staff to filter the pivot tables as required. Unprotecting the sheet, allows the refresh to work with out any code.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,279
Messages
5,527,741
Members
409,786
Latest member
AbdulMoix

This Week's Hot Topics

Top