Automate Pivot Table Data > Graph

English
, , ,
#1

Hi, looking for some help with a CALC problem I’m facing…

I’m trying to automate the process of pulling data from a pivot table to create/update a graph automatically. I’m also looking to have the raw response data that feeds the pivot table update as it gets entered or via a button/macro.

I read a post somewhere about creating a Pivot Table Refresh button, which I now have in my toolbar. But as stated I would like to automate the entire process.

I’ve tried recording a macro with the mouse and menu/toolbar options but it hasn’t worked as I expected a macro to work. Here is what I’ve got so far…

Sheet 1
• Raw Response Data (a grid that won’t grow inline items, so this is a fixed quantity, I’ll just be updating the responses over time i.e. there will only ever be 100 responses)

Sheet 2
• Pivot Table
• An Extraction Table (values from the pivot table, copied with a simple = formula)
• Graph Table (Extraction Table data without formulas, manually populated via ‘Paste Special = Values’ to strip out the formulas from the Extraction table

Sheet 3
• The Graph - drawn from Graph Table data in Sheet 2

Am I going about this the wrong way? I welcome some guidance/advice. Thanks!

#2

Have you taken a look at Creating Pivot Charts

#3

Hi @mariosv
Thanks for the reply. At the time of writing the post I had not checked out the link you’d sent - so first and foremost, thank you for that!

Since reviewing and testing the Pivot Chart function you kindly shared - as an option, I’m not sure it suites my needs. Unless there is a way to remove the imbedded filter(s) from the Chart itself after creation. Do you know of a way to do this?

The end result I’m looking for is a clean chart/graph without any other elements distracting the viewer.

The focus of my post is more with the MACRO and the ability to automate the following steps so that I can focus on just inputting results/transactional data.

I essentially want the Macro to do the following:

  1. Auto Refresh the Pivot Table (Sheet 2) - from the recently updated Raw Response Data in Sheet 1
  2. Update the graph(s) on Sheet 3 that use the Pivot Table (Sheet 2) as the source data
  3. Save graph locally as a .png file for near instantaneous display / use
#4 
Sub Sheet_OnFocus()
'''	Called by: OnFocus (Activate Document) event of this sheet.
	Call RefreshPivotTable
End Sub

Sub RefreshPivotTable()
'''	Called by: Sheet_OnFocus
	On Local Error GoTo HandleErrors
	Dim oSheet As Object, oTables As Object, oTable As Object

	oSheet = ThisComponent.Sheets.getSheetByName("MySheet")
	oTables = oSheet.DataPilotTables
	oTable = oTables(0)  'if only one table on this sheet (DataPilot1)
	oTable.refresh
	Exit Sub
	
HandleErrors:
	Msgbox "Error" & Err & ": " & Error _
	 , MB_ICONSTOP, "macro:RefreshPivotTable"
End Sub

The pivot chart is updated automatically without your participation. So are the formulas.

Refreshing a regular chart will have problems if the pivot report size changes when the data is refreshed. It looks like the chart data source reference needs to be updated. Yes exactly. This is a lot of dirty work in calculating the range. Is that how you want it? Better to leave the pivot chart.

Demo code:

    Dim oDrawPage As Object, oDraw As Object
    Dim args(1) as new com.sun.star.beans.PropertyValue
    Dim gef: gef = CreateUnoService("com.sun.star.drawing.GraphicExportFilter")
    Dim idx%  'chart index
    ...
    oDrawPage = oSheet.getDrawPage()
    oDraw = oDrawPage.getByIndex(idx)  '<- your chart shape

    args(0).Name = "URL"
    args(0).Value = ""  'your URL to PNG file goes here: file:///document-path
    args(1).Name = "MediaType"
    args(1).Value = "image/png"
    gef.setSourceDocument(oDraw)

    gef.filter(args)
#5

Pivot charts do not require any intervention. The adjust to changes in size and layout.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.