Printing PivotChart using VBA Code

[JrMontgom]

I have created a pivot chart using VBA 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.

[June7]

You want to print a form object?

Have you tried any print code, such as:

DoCmd.PrintOut

or

DoCmd.RunCommand acCmdPrint

I've never used pivot charts, just graph control.

[JrMontgom]

You want to print a form object?

Have you tried any print code, such as:

DoCmd.PrintOut

or

DoCmd.RunCommand acCmdPrint

I've never used pivot charts, just graph control.

thanks June I guess it was so complicated using Pivot Charts Ifigured printing one would be real complicated I forgot the KISS principle ;-) I used DoCmd.PrintOut and it worked but the DoCmd.RunCommand acCmdPrint gave mean error code 2174. Nowhow to I mark this solved????

[June7]

Thread Tools dropdown above first post. Done.