Auto refresh pivot table or macro tied to event

FrogFan · September 18, 2023, 8:51pm

Hello,

I’ve been chasing around the internet for several hours now and it seems like it’s time to ask the experts here for advice.

I have a LO Calc document that contains several sheets. One of the sheets contains a pivot table. As the underlying data changes, the data in the pivot table changes, but, to see those changes, I need to refresh the pivot table. I’m quite capable of doing that myself, buy my user is not, so I’m looking for a way to cause the pivot table to refresh “automatically” when underlying data changes or in response to some other useful trigger.

I have looked for “auto-refresh” of a pivot table, and I’m pretty sure what I’ve learned is that that functionality is not currently available. Is that right?

Assuming the direct “auto-refresh” is not available, I created a macro that pushes the “refresh” button given the appropriate sheet is active and have attempted to tie it to various events in the sheet that contain the pivot table. If I open that sheet, select a cell in the pivot table, and run the macro, it works. But there doesn’t appear to be any event I can tie it to that reliably executes it when that event happens. I’ve tried “Activate Document”, “Deactivate Document”, right click, content changed, and various other events. Sometimes the recalculation happens and sometimes it does not.

I’ve looked for quite awhile for an answer to this second question with no joy either. There appears to be no event I can associate my macro with that would cause it to run.

I’m sure I’m doing something wrong, and I look forward to any advice from the experts.

Thanks in advance.

Lupp · September 19, 2023, 12:10pm

To refresh all pivot tables contained in a newly opened Calc document doesn’t require a button or the like. You may do as described below commenting the posted code.

REM The function below may be assigned as the event handler
REM for onViewCreated on the document level.
REM This should not prevent a probably set onViewCreated handler
REM on the application level from being excecuted.

Function refreshAllPivotTables(pEvent) As Boolean
refreshAllPivotTables = False
On Local Error Goto fail
cDoc    = pEvent.Source
sheets  = cDoc.Sheets
For Each sheet In sheets
 pivTs   = sheet.DataPilotTables
 For Each pivT In pivTs
  pivT.refresh()
 Next pivT
Next sheet
fail:
End Function

It’s a bit more complicated to automatically refresh specific pivot tables if anything in their source data was changed. And it’s inefficient.

jfn · September 19, 2023, 5:46am

Hi,

AFAIK, I’m afraid there’s no pivot table auto-refresh mechanism.

Second, WRT the event to be called: the pivot table must be refreshed any time the source data are changed. Thus the event is not a document one, but a sheet event.
Right click on the sheet tab, then select Sheet events. The desired one is Content changed.

Note that:

this is a sheet-based event.
it is called for any change in the sheet, wherever on the chosen sheet. Thus it might be resource-hungry and you should test for the changed cell-s before calling the pivot table refresh process.

HTH

jfn · September 19, 2023, 5:51am

As a side note: there’s a DyntablesUpdater extension that allows to refresh a pivot table whith clicking some toolbutton
See here: DyntablesUpdater » Extensions

FrogFan · September 19, 2023, 2:54pm

Hello and thank you all for your assistance.

After submitting this question, and before receiving any responses, I decided to do a Google search of the same question specifying OpenOffice Calc instead of LibreOffice calc. Doing so produced a helpful response by Villeroy here. He suggested the following macro:

Sub refresh_PivotTables()
for each pv in ThisComponent.CurrentController.ActiveSheet.DataPilotTables
  pv.refresh()
 next
End Sub

I added this macro to my document and associated it with the “Activate Document” sheet event for the sheet containing (only) the pivot table and it seems to work exactly as I had hoped, with no perceptible impact on performance.

When I created my macro, I used the “Record Macro” option of LO. So, on clicking “Record Macro” with the sheet containing the pivot table active, I selected Data>Pivot Table>Refresh then “Stop Recording”. That produced the following macro:

sub TestRefresh
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:RecalcPivotTable", "", 0, Array())


end sub

This is probably well off the subject topic, but it seems like this macro is useless, as the “refresh” piece of code is commented out. I uncommented it and tested it with the appropriate sheet active and it seemed to work, but it didn’t work when I associated it with the “Activate Document” event of the sheet.

Maybe I need to find a thread or post another question about how to use the “Record Macro” functionality and associate it with an event.

Again, thanks for your interest and assistance.

sokol92 · September 19, 2023, 3:22pm

Note the commented statement:

rem dispatcher.executeDispatch(document, ".uno:RecalcPivotTable", "", 0, Array())

The comment means that the macro recorder is “not sure” that the result of executing the recorded code will be identical to the result of the user’s interactive actions.
Of course, you should use calls to UNO methods instead of calls to Dispatcher commands whenever possible.