I have created a pivot chart using VBA Code.
Code:
Function BuildPivotChart(Frm As Form, strQry As String, strAr() As String) As Boolean
Dim objPivotChart As OWC10.ChChart
Dim objChartSpace As OWC10.ChartSpace
Dim bResult As Boolean
Dim strExpression As String
Dim strvalues As String
Dim rst As ADODB.Recordset
Dim strMYr As String
Dim intCt As Integer
Dim axCategoryAxis 'X axis
Dim axValueAxis 'Y axis
Dim strcurCEF As String
'Open the form in PivotChart view.
' DoCmd.OpenForm "frmPivotChart", acFormPivotChart
On Error GoTo ErrorHandler
Const CALLER As String = " basPivotChart:BuildPivotChart "
'Set frm = Forms("frmPivotChart")
If IsLoaded(Frm.Name) Then
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseClient
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open strQry, Options:=adCmdText
intCt = rst.RecordCount
Debug.Print intCt
'Loop through Recordset to obtain data for the chart and put intp 2 strings. for Y (strExpression) and X (values)
If intCt > 0 Then
rst.MoveFirst
Do While Not rst.EOF
'format the data expression = dollars value =Month
'Chg by Admin on 1/27/2015 in basPivotChart at 11:54
'Reason/Purpose:Ck for nulls in fields set to "" or 0
strcurCEF = CStr(Nz(rst.Fields("CEFAMT").Value, 0))
' strcurCEF = Format(strcurCEF, "$0##,###.00")
strExpression = strExpression & strcurCEF & ","
'strMYr = GetAlphaMonth(Month(CStr(rst.Fields("DATESTART").Value))) & " / " & Year(CStr(rst.Fields(0).Value))
strMYr = CStr(Nz(rst.Fields("DateStart"), vbNullString))
strvalues = strvalues & strMYr & ","
rst.MoveNext
Loop
Debug.Print strExpression
Debug.Print strvalues
'Trim any extra tabs from string.
strExpression = Left(strExpression, Len(strExpression) - 1)
strvalues = Left(strvalues, Len(strvalues) - 1)
Debug.Print Frm.Name
'Clear existing Charts on Form if present and add a new chart to the form.
'Set object variable equal to the new chart.
Set objChartSpace = Frm.ChartSpace
objChartSpace.Clear
objChartSpace.Charts.Add
Set objPivotChart = objChartSpace.Charts.item(0)
'Set a variable to the Category (X) axis.
Set axCategoryAxis = objChartSpace.Charts(0).Axes(0)
' Set a variable to the Value (Y) axis.
Set axValueAxis = objChartSpace.Charts(0).Axes(1)
' The following two lines of code enable, and then
' set the title for the category axis.
axCategoryAxis.HasTitle = True
axCategoryAxis.Title.Caption = "Date for " & strAr(0)
' The following two lines of code enable, and then
' set the title for the value axis.
axValueAxis.HasTitle = True
axValueAxis.Title.Caption = GetCEFCodeStr(strAr(1)) & " , in U.S. Dollars" 'Example "Begining Balance in U.S. Dollars"
'Add Series to Chart and set the caption.
objPivotChart.SeriesCollection.Add
objPivotChart.SeriesCollection(0).Caption = "Endowment Fund " & strAr(1) & " " & "Balance"
'Put Red border around plotted values
objPivotChart.SeriesCollection(0).Border.Color = vbRed
'Add Data to the Series.
objPivotChart.SeriesCollection(0).SetData chDimCategories, chDataLiteral, strvalues
objPivotChart.SeriesCollection(0).SetData chDimValues, chDataLiteral, strExpression
bResult = True
'Set focus to the form and destroy the form object from memory.
Else
Call MsgBox("Sorry couldn't find any records for data entered.", vbCritical, "CEF Pivot Chart")
End If
Cleanup:
'frm.SetFocus
' Set frm = Nothing
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close
Set rst = Nothing
End If
BuildPivotChart = bResult
Exit Function
ErrorHandler:
MsgBox Err.Description & vbCrLf & _
Err.Number & vbCrLf & _
"Called By :" & CALLER & vbCrLf & _
Err.Source, VbMsgBoxStyle.vbCritical, "Could not add new name to data base" & vbCrLf & _
"Module Name: = " & MODULENAME
bResult = False
GoTo Cleanup
Else
Call MsgBox("Form not loaded, try again", vbCritical, "Pivot Chart")
End If
I pass the parameters to get the correct data in the array strAr. Everything works and I am able to change the info in the strAr array to view various PivotCharts based on the data in the array but I can't figure out how to print the resulting PivotChart. I got the code to create the PivotChart from this forum but I forgot to credit the programmer. Mea Culpa but I am sure I accidently erased that information in a crazed moment while dealing with the PivotTable/Chart controls. Any help would be appreciated.