How can I make a simple macro to update a Pivot table and then create a click button

[freed1b]

Hello, I imported an excel spread sheet that has a macro that when clicked refreshes a pivot table. The button does not work in Libre and I have tried to recreate it using the record macro feature but I can’t get it to work

The following is a copy of my macro contents. Using Cal version 4.4.0.3

Thanks in advance for your help. I have an assignment that needs to be handed in on Monday morning and this is holding me back.

sub Update_Data
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 ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = “Nr”
args1(0).Value = 4

dispatcher.executeDispatch(document, “.uno:JumpToTable”, “”, 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = “ToPoint”
args2(0).Value = “$B$34”

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args2())

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

end sub

[Rugslug]

Right click on the pivot table, select refresh. Does that produce the desired result as well?

[freed1b]

Yes it does. I was hoping to create a macro and assign it to a push button on another tab - such that the updating is behind the scenes.

Thanks for your prompt response

[mariosv]

Please take a look to this thread it has macros for update pivot tables.

Autorefresh DataPilot Tables

[freed1b]

Thanks for the Information I used this and it worked like a charm
Sub update_PT

sheet = thisComponent.Sheets.getByName(“Sheet1”)
PT = sheet.DataPilotTables.getByIndex(0) ’ put (1) (2) etc to update second,third etc. pivot table in sheet
PT.refresh()

end sub