automatic update/refresh of all pivot tables at file opening: how?

[Wololo]

Hi,
I have one LO calc file with severall sheets:

1. one sheet with my “transactions” : a few columns and many rows. The data is “referenced” within a named range to be used in several pivot tables, so that the pivots have no issues if i add new records.
2. 16 other sheets having different pivot tables, all analysing data of the same unique named range from point 1.

I would like that all these pivot tables automatically refresh when I open the file, so that I don’t have to go manually in each single pivot table to refresh the data (Right-click > Refresh).
in MS Excel 2007 I can tick a check box in each single pivot table properties for this auto-update at file opening.

See this screenshot from Excel which I would like to find in LibreOffice:

Has anyone any advices?
Thanks in advance

Libre Office Calc 5.1.4.2

[PYS]

Hi

With Tools▸Customize▸Events tab, you can assign a macro to the Open Document event:

Sub RefreshPilot
dim oSheet as object, oPilot as object

for each oSheet in thiscomponent.sheets
	for each oPilot in oSheet.DataPilotTables
		oPilot.refresh
	next oPilot
next oSheet
end sub

Of course, Tools▸Options▸LibreOffice▸Security▸Macros Security must be set to allow execution

Regards

[mark_t]

You might also add the macro to the toolbar so you can easily refresh all the pivot tables after you have entered new data instead of closing and re-opening the document. Use Tools, Customise..., select the Toolbar tab, select save in the document instead of save in Calc as you only need to use this for the document that has pivot table. Scroll to the end of the list of commands, click on Add, in category under LibreOffice Macros, find the macro and then click on Add.

[Wololo]

thank you @Profile - PYS - Ask LibreOffice

Is there any option to solve this question without macro ?

Regards

[Downunder]

The Sheet toolbar menu has a Sheet Events item. The macro to refresh mentioned already could be added to the Activate event. I have not tried it but would be worth exploring especially say if you also want to have a cell with a timestamp of last refresh.

Another issue is when to refresh. I have a large collection of data worksheets with each consolidating to a single row. Another worksheet collates the total rows of each worksheet by using a series of indirect statements with sheet name as a variable. My pivot table is on the collated worksheet. A minor edit to any of the data worksheets could affect the pivot.

My solution was to have a cell above the pivot which compared the number of rows and total dollars from my consolidation tag with the pivot. The cell value was “Please refresh” on mismatch with conditional formatting changing colour, etc.