Results 1 to 4 of 4
  1. #1
    JrMontgom is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Location
    Vero Beach, FL USA
    Posts
    124

    Cool Printing PivotChart using VBA Code

    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JrMontgom is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Sep 2012
    Location
    Vero Beach, FL USA
    Posts
    124

    Cool Printing Pivot Chart

    Quote Originally Posted by June7 View Post
    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????

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Thread Tools dropdown above first post. Done.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Blurry PivotChart
    By RachelBedi in forum Forms
    Replies: 1
    Last Post: 11-14-2012, 02:01 PM
  2. PivotChart - LIMITS
    By cap.zadi in forum Forms
    Replies: 7
    Last Post: 11-29-2011, 02:17 AM
  3. Replies: 3
    Last Post: 10-19-2011, 01:05 PM
  4. PivotChart presentation
    By wharting in forum Forms
    Replies: 5
    Last Post: 08-25-2011, 11:07 PM
  5. Looping code for printing reports
    By Lockrin in forum Access
    Replies: 2
    Last Post: 02-09-2010, 05:48 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums