Ask Your Question
0

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

asked 2016-11-07 12:28:21 +0200

Wololo gravatar image

updated 2016-11-10 14:01:28 +0200

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:

image description

Has anyone any advices? Thanks in advance

Libre Office Calc 5.1.4.2

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-11-10 15:08:51 +0200

pierre-yves samyn gravatar image

Hi

With ToolsCustomizeEvents 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, ToolsOptionsLibreOfficeSecurityMacros Security must be set to allow execution

Regards

edit flag offensive delete link more

Comments

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.

mark_t gravatar imagemark_t ( 2016-11-10 17:30:42 +0200 )edit
0

answered 2017-08-27 22:55:42 +0200

Wololo gravatar image

thank you @https://ask.libreoffice.org/en/users/15459/pierre-yves-samyn/

Is there any option to solve this question without macro ?

Regards

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-07 12:28:21 +0200

Seen: 1,737 times

Last updated: Aug 27 '17