GabrielJNP
New member
- Local time
- Today, 23:03
- Joined
- Jun 14, 2012
- Messages
- 1
Hi all,
Like many before me (it would appear), I have come unstuck while trying to automate Pivot Charts in Access via VBA. Many hours of searching the 'Net and experimenting, going through help files, etc. have got me to the point where I believe there are two approaches:
1. OWC (I have tried OWC.11 and OWC.10)
2. Standard Access object methods
I am trying to build a PivotChart programmatically using an existing form (e.g. pvtSummarySum) whose RecordSource is an existing query (e.g. qrySummarySum). I have got so far as to create the chart, format it, etc. but for the life of me cannot alter the chart's AutoCalc/Totals state. At the point of binding the Values, the chart automatically is set to a default Total type of "Sum". What I want to do is change that to one of the other options (Count, Min, Max, Avg, Variance, St Dev, etc.) I think that in OWC the item class is PivotTotalFunction, while in Access the "RunCommand acCmdPivotAutoCount" or similar might be the way to go, but I can't find any reference giving an example of the syntax to use for the OWC solution. The alternative (RunCommand) won't work unless the Field Button is selected, and I can't find a way to do that programmatically either...
:banghead:
If anyone has any suggestions, I am all ears!
Many thanks in advance.
Edit: I forgot to mention: I am working in Access 2007, for what it's worth - and have referenced the OWC .dll. I am calling the chart building process from a form's Sub activated by a button. The actual chart building is done in a Function I've written to which I pass several variables: - the Function has a syntax of BuildPivotChart(ByRef strFormName As String, strHeading As String, strChartType As Integer, strTotalType As String, strSeries As String, strColumnCats As String, strColumnVals As String) where strTotalType is either "Sum", "Count", "Min", "Max", "Average", "Deviation" or "Variance" and the variable I want to use to set the Pivot's Autocalc/Total type.
Like many before me (it would appear), I have come unstuck while trying to automate Pivot Charts in Access via VBA. Many hours of searching the 'Net and experimenting, going through help files, etc. have got me to the point where I believe there are two approaches:
1. OWC (I have tried OWC.11 and OWC.10)
2. Standard Access object methods
I am trying to build a PivotChart programmatically using an existing form (e.g. pvtSummarySum) whose RecordSource is an existing query (e.g. qrySummarySum). I have got so far as to create the chart, format it, etc. but for the life of me cannot alter the chart's AutoCalc/Totals state. At the point of binding the Values, the chart automatically is set to a default Total type of "Sum". What I want to do is change that to one of the other options (Count, Min, Max, Avg, Variance, St Dev, etc.) I think that in OWC the item class is PivotTotalFunction, while in Access the "RunCommand acCmdPivotAutoCount" or similar might be the way to go, but I can't find any reference giving an example of the syntax to use for the OWC solution. The alternative (RunCommand) won't work unless the Field Button is selected, and I can't find a way to do that programmatically either...
:banghead:
If anyone has any suggestions, I am all ears!
Many thanks in advance.
Edit: I forgot to mention: I am working in Access 2007, for what it's worth - and have referenced the OWC .dll. I am calling the chart building process from a form's Sub activated by a button. The actual chart building is done in a Function I've written to which I pass several variables: - the Function has a syntax of BuildPivotChart(ByRef strFormName As String, strHeading As String, strChartType As Integer, strTotalType As String, strSeries As String, strColumnCats As String, strColumnVals As String) where strTotalType is either "Sum", "Count", "Min", "Max", "Average", "Deviation" or "Variance" and the variable I want to use to set the Pivot's Autocalc/Total type.
Last edited:
