Power Pivot not refreshing on opening. Excel 2016

[SueBristow]

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?

 

[RoryA]

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:

       With ThisWorkbook.Model
   .Initialize
   .Refresh
End With

 

[SueBristow]

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]

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]

Perhaps add:

Thisworkbook.RefreshAll

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

 

[SueBristow]

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.