Update All Pivot Tables When Opening File In ODS Format?

[PaulK]

I have seen this asked before, but believe there may be other options to automatically update pivot tables when opening a spreadsheet file. Of the suggestions, a macro seems to be offered and I am looking for a way without using (enabling) macros.

I am on 7.3.2.2 Librecalc and have an outsourced .xlsx file that updates as I like. I don’t think it uses a macro as I checked that my security setting for macros was set to highest setting. If I save this file in ODS format, it does not auto update when loading. If I re-save this ods file back in xlxs format, it goes back to auto updating when loading.

See file sourced through here: https://www.youtube.com/watch?v=MRtHNqafufg

If I create a new spreadsheet with pivot tables and save in xlsx format, it does not auto update. While I may be able to repurpose the test file for my use, I would like to learn how it was done. I would prefer this to work in ods format, but as a plan B to use this feature in xlsx would be almost as good

Any direction would be appreciated. Thanks!

[sokol92]

In Excel, the update of pivot tables when opening a file is set using the RefreshOnFileOpen property (you can also set it in the dialog for the properties of the pivot table).
As far as I know, there is no similar property for pivot tables in LO Calc. However, this is easy to do with a macro called when the file is opened.

[Lupp]

Code that would do it:

Sub refreshAllContainedPivotTables()
doc     = ThisComponent
sheets  = doc.Sheets
For Each sheet In sheets
 dpts  = sheet.DataPilotTables
 For Each dpt In dpts
  dpt.refresh()
 Next dpt
Next sheet
End Sub

[PaulK]

Thanks for the information.

@sokol92, can I assume the Excel RefreshOnFileOpen property is active in LO, but LO doesn’t have a way to change that property? The xlsx format pivot tables update in LO, but not the ODS format. With macros disabled, I don’t get a warning about it when opening (and updating the tables) the xlsx format file.

Thanks @Lupp, your code updates my tables. I just wish I could get there without using a macro.

[sokol92]

@PaulK

can I assume the Excel RefreshOnFileOpen property is active in LO

The Calc object model is fundamentally different from the Excel object model. When you open an .xlsx file, Calc creates a pivot table (in its own format, of course) and populates it based on the data in the .xlsx file.
When you open an .ods file, Calc builds the pivot table as it was when the file was saved.

[Lupp]

@PaulK

I just wish I could get there without using a macro.

I feel sure there is no way. You can, however, avoid document macros if you create some related code in a module of your local Standard library. (or on any system expected to do the trick) and call the respective Sub as event handler for ViewCreated. Whether the refresh should run for a specific document you can (e.g.) choose then by creating a UserDefinedProperty, say refreshPivotTablesOnOpening with the value 1.
(I don’t actually understand the need. I would expect that the document, when saved with new data, should already have refreshed Pivot tables. Nonetheless I tested the above concept for you. It worked as expected.)

[PaulK]

Thank you for your help. I will look into this.

I may be doing something wrong, but before your help, the only way my newly created pivot tables update is by selecting a cell within each pivot table and selecting Refresh. After changing source data and saving the file, reopening the file and the tables (and charts based on those tables) still indicate old, unchanged data. I cannot find an option to refresh other than manual.

[Lupp]

@PaulK

…still indicate old, unchanged data.

I’m losing the context.

• Did you address your comment above to me or to @sokol92?

• When were the “old, unchanged data” entered?
  If you refreshed the pivot tables before saving, they should surely also be in that “refreshed” state after reopening.
  Did somebody else change data in-between? Are there randomly generated “data”? Are there external data linked in?

• Did you actually assign a handler to “View Created” via Customize? If so: What code? Where stored?

• Are your charts directly based on data from the pivot tables, or on data calculated (partly) from their results. (Charts based on pivot tables have a different UI.)

• Can you provide (attach) an example documnent?

In my mentioned test environment everything works as expected.

[PaulK]

@Lupp, I selected the reply icon below your comment and assumed my post would indicate that I was replying to you.
So, a macro is needed. I have added your code and can get the tables to update, but when you left the statement (and I quote)…

@Lupp

(I don’t actually understand the need. I would expect that the document, when saved with new data, should already have refreshed Pivot tables.

… It left me to believe you think the tables should already be updates with new data before saving the file. When you used the word “need”, I felt you meant the -need for a macro- at all. Again, initially, I wanted the Pivot tables to update automatically without using a macro.

I will use a form of your code (thanks again) and move on.

[eeigor]

A macro is needed. The handler must be connected to the sheet activation event.

Sub Sheet_OnFocus()
'''	Called by: OnFocus (Activate Document) event of this sheet.
	Call RefreshPivotTable
End Sub

Sub RefreshPivotTable()
'''	Called by: Sheet_OnFocus
'''	Calls: GetSheetByCodeName()

	On Local Error GoTo HandleErrors
	Dim oSheet As Object, oTables As Object, oTable As Object

	oSheet = GetSheetByCodeName("PivotTable")
	oTables = oSheet.DataPilotTables
	oTable = oTables(0)  'only one table on this sheet (PivotTable1)
	oTable.refresh
	Exit Sub
	
HandleErrors:
	Msgbox "Error" & Err & ": " & Error _
	 , MB_ICONSTOP, "macro:RefreshPivotTable"
End Sub

Function GetSheetByCodeName(sCodeName$, Optional oDoc As Object) As Object
	If IsMissing(oDoc) Then oDoc = ThisComponent

	Dim oSheet As Object
	For Each oSheet In oDoc.Sheets
		If oSheet.CodeName = sCodeName Then
			GetSheetByCodeName = oSheet
			Exit For
		End If
	Next
End Function

If you have more than one pivot table on a sheet, add a loop like @Lupp suggested.
Yes, I usually create a separate module for the sheet. In your case, you need to change the code a bit.

[PaulK]

Thanks @eeigor