The following code adds the CustomerID, ShipVia, and Freight fields to the row, column, and detail areas of the PivotTable view of a form that is based on the Orders table.

Dim fset1, fset2, fset3 As PivotFieldSet

Set fset1 = Me.PivotTable.ActiveView.FieldSets("CustomerID")
Set fset2 = Me.PivotTable.ActiveView.FieldSets("ShipVia")
Set fset3 = Me.PivotTable.ActiveView.FieldSets("Freight")

Me.PivotTable.ActiveView.RowAxis.InsertFieldSet fset1
Me.PivotTable.ActiveView.ColumnAxis.InsertFieldSet fset2
Me.PivotTable.ActiveView.DataAxis.InsertFieldSet fset3

The following code creates a total field that counts the number of CustomerIDs, and adds the field to the detail area of the view.

Me.PivotTable.ActiveView.AddTotal "Count Of Customers", _
    Me.PivotTable.ActiveView.FieldSets("CustomerID").Fields("CustomerID"), _
    plFunctionCount

Me.PivotTable.ActiveView.DataAxis.InsertTotal _
    Me.PivotTable.ActiveView.Totals("Count Of Customers")

The following code creates a total field that calculates 7% of the freight value, and adds the field to the view’s detail area. The expression argument will accept any valid Microsoft Jet Database Engine expressions with the ‘safe’ Visual Basic for Applications (VBA) functions.

Me.PivotTable.ActiveView.AddCalculatedTotal "FTax", "Freight Tax", "[Freight] * 0.07"
Me.PivotTable.ActiveView.DataAxis.InsertTotal Me.PivotTable.ActiveView.Totals("FTax")

 Note   Attempting to run this code more than once will result in an error message.

The following code clears all fields and totals from a PivotTable view, leaving it blank. The basic idea is to loop through the various axes and remove all totals and fields.

 Note   This sample code does not remove formatting that may be applied to the fields and totals.

Dim ptable As PivotTable
Set ptable = Me.PivotTable
With ptable.ActiveView
    Do While .RowAxis.FieldSets.Count > 0
        .RowAxis.RemoveFieldSet 0
    Loop

    Do While .ColumnAxis.FieldSets.Count > 0
        .ColumnAxis.RemoveFieldSet 0
    Loop
    Do While .FilterAxis.FieldSets.Count > 0
        .FilterAxis.RemoveFieldSet (0)
    Loop
    Do While .DataAxis.FieldSets.Count > 0
        .DataAxis.RemoveFieldSet (0)
    Loop
    Do While .DataAxis.Totals.Count > 0
        .DataAxis.RemoveTotal (0)
    Loop
End With

The following code filters the CustomerID field, which is in the row area. You pass the view an array of the values you would like to see by using the IncludedMember property.

Add this code to the Form_DblClick(Cancel As Integer) event. To run this code, double-click the form after you open it.

Dim arrFilter As Variant

arrFilter = Array("ALFKI", "BLAUS", "CHOPS", "EASTC")

Me.PivotTable.ActiveView.RowAxis.FieldSets("CustomerID") _
    .Fields("CustomerID").IncludedMembers = arrFilter

The following code sorts the items in the row field based on the custom order you define. To create the order, you specify the order and the direction in which you would like to sort the items.

Add this code to the Form_DblClick(Cancel As Integer) event. To run this code, double-click the form after you open it.

Dim arrSort As Variant

arrSort = Array("Vice President, Sales", "Sales Manager", _
              "Sales Representative", "Inside Sales Coordinator")

Me.PivotTable.ActiveView.RowAxis.FieldSets("Title").Fields("Title") _
              .OrderedMembers = arrSort

Me.PivotTable.ActiveView.RowAxis.FieldSets("Title").Fields("Title") _
              .SortDirection = plSortDirectionCustom

The following code formats subtotals in a view. You use the reference to the field to which the subtotal belongs and then adjust the subtotal properties accordingly.

Me.PivotTable.ActiveView.rowaxis.FieldSets(0).Fields(0).SubtotalBackColor = "Red"
Me.PivotTable.ActiveView.rowaxis.FieldSets(0).Fields(0).SubtotalFont.Bold = True

The following code formats fields in the detail area. The first line sets the background color of the first detail field in the view, and the second line sets a custom number format for the field.

Me.PivotTable.Activeview.DataAxis.FieldSets(0).Fields(0).DetailBackColor = "Green"
Me.PivotTable.ActiveView.DataAxis.FieldSets(0).Fields(0).NumberFormat = "$#,##0.00"

 Note   You must run the code to set the number format of a field in a PivotTable view each time the object is opened in the view.

The following code hides and shows details for all items in the view.

Me.PivotTable.ActiveData.HideDetails
Me.PivotTable.ActiveData.ShowDetails

If you want to show or hide details for a subset of the data in the view, use the ShowDetails and HideDetails methods on the ChildRowMembers object. The following code shows and hides details for the eighth item in the row area.

Me.PivotTable.ActiveData.RowAxis.RowMember _
       .ChildRowMembers(7).ShowDetails

Me.PivotTable.ActiveData.RowAxis.RowMember _
       .ChildRowMembers(7).HideDetails

The following code shows or hides the drop areas in a PivotTable view. Unlike PivotChart view, PivotTable view allows you to show and hide specific drop areas.

Me.PivotTable.ActiveView.DataAxis.Label.Visible = False
Me.PivotTable.ActiveView.RowAxis.Label.Visible = True
Me.PivotTable.ActiveView.ColumnAxis.Label.Visible = True
Me.PivotTable.ActiveView.FilterAxis.Label.Visible = False

Use the following code to export a PivotTable view to Excel. You may want to do this to take advantage of Excel’s more advanced pivoting features, such as printing.

Me.PivotTable.Export "Your File Name Here", plExportActionOpenInExcel

The following code exports a .gif image of a PivotTable view. Exporting an image of the view allows you to share a static image of the view with other users.

'1024 X 1024 is the size of the view.

Me.PivotTable.ExportPicture "C:\yourdirectory\filename.gif", , 1024, 1024

The following code adds fields to the category and value drop areas on the view.

Add this code to the Form_Open(Cancel As Integer) event handler of a form whose default view is set to PivotChart view.

Me.ChartSpace.SetData c.chDimCategories, c.chDataBound, "OrderDate"
Me.ChartSpace.SetData c.chDimValues, c.chDataBound, "Freight"

The following code enables multiple charts and sets all charts in the chartspace to have a unified scale.

forms(0).Form.ChartSpace.HasMultipleCharts = True
forms(0).Form.ChartSpace.HasUnifiedScales = True

The following code splits the chart’s axis so that the small values and high values are displayed in a logical manner. The split is actually set in the chScaling object associated with the axis.

Add the following code to the form’s Form_Open(Cancel As Integer) event handler.

Dim sc As ChScaling
Set sc = Me.ChartSpace.Charts(0).Axes(chAxisPositionLeft).Scaling

sc.HasSplit = True
sc.SplitMinimum = 1
sc.SplitMaximum = 3

The following code clears all charts from the chartspace and builds a literal chart. A literal chart is not bound to any data; it is for demonstration purposes only. This code also turns off the chart’s selection marks.

Me.ChartSpace.Clear
Me.ChartSpace.BuildLitChart
Me.ChartSpace.Charts(0).Type = chChartTypeColumnClustered

The following code executes a sort command on the first field in the chart's series axis.

Dim ser As ChSeries
Set ser = Me.ChartSpace.Charts(0).SeriesCollection(0)

ser.Select
Me.ChartSpace.Commands(chCommandSortDescending).Execute

The following code adds a title to the chartspace and formats the title. If the chartspace contains multiple charts, use a similar approach to add titles to each chart.

Me.ChartSpace.HasChartSpaceTitle = True
With Me.ChartSpaceTitle
    .Caption = "Here's Your Title"
    .Interior.Color = vbWhite
    .Border.Color = vbBlack
    .Border.DashStyle = chLineDashDot
    .Font.Size = 16
    .Font.Name = "verdana"
    .Font.Color = vbGreen
End With

The following code moves the axes from left to right and top to bottom, and then changes the scale of an axis from linear to log.

Me.ChartSpace.Charts(0).Axes(0).Position = chAxisPositionTop
Me.ChartSpace.Charts(0).Axes(1).Position = chAxisPositionRight
Me.ChartSpace.Charts(0).Axes(0).Scaling.Type = chScaleTypeLogarithmic

The following code adds format maps to an Access form in PivotChart view. Add the code to the Form_Open(Cancel As Integer) event handler of a form whose default view is set to PivotChart view.

' The following three lines of code add fields to the chart. The third value is
' the format value, which will be used to format data points. In this example,
' the value of freight will be color-coded to display the relative ranking of a
' given data point's value.
Me.ChartSpace.SetData c.chDimCategories, c.chDataBound, "OrderDate"
Me.ChartSpace.SetData c.chDimValues, c.chDataBound, "Freight"
Me.ChartSpace.SetData c.chDimFormatValues, c.chDataBound, "Freight"

' Add a format map and then set the format map's properties. The result is that
' data points with higher values will have red shading, and data points with
' lower values will have blue shading.
Dim ch
Set ch = Me.ChartSpace.Charts(0)
ch.SeriesCollection(0).FormatMap.Segments.Add
ch.SeriesCollection(0).FormatMap.Segments(0).Begin.Interior.Color = vbRed
ch.SeriesCollection(0).FormatMap.Segments(0).End.Interior.Color = vbBlue
ch.SeriesCollection(0).FormatMap.Segments(0).HasDiscreteDivisions = True

' Set the chart type to Column Clustered.
ch.Type = chChartTypeColumnClustered

' Enhance the appearance of the chart.
With Me.ChartSpace
    .DisplayFieldButtons = True
    .DisplayToolbar = False
    .HasChartspaceLegend = True
    .HasChartSpaceTitle = True
    .ChartSpaceTitle.Caption = "Freight Totals Over Time"
End With

The following code hides the chart’s drop areas.

Me.ChartSpace.DisplayFieldButtons = False

The following code adds a legend to the view, formats it, and then moves it to the bottom of the view.

Me.ChartSpace.Charts(0).HasLegend = True
With Me.ChartSpace.Charts(0).Legend
    .Interior.Color = vbBlack
    .Font.Color = vbWhite
    .Border.Color = vbGreen
End With
Me.ChartSpace.charts(0).Legend.Position = chLegendPositionBottom

The following code adds minor gridlines to axis 0.

Me.ChartSpace.Charts(0).Axes(0).HasMinorGridlines = True

The following code demonstrates how to draw objects and add text to a form in PivotChart view. This example calculates the average of several points in a series and draws a line at that value on the chart.

Add this code to the Form_Open(Cancel As Integer) event handler of a form whose default view is set to PivotChart view. Add fields to the drop areas and add a legend to the chart.

' This code causes the chartspace to repaint, which fires the form's
' AfterFinalRender event. This event handler will draw objects and
' add text to the chart.
Me.ChartSpace.repaint

Add the following code to the Form_AfterFinalRender(drawObject as object) event handler.

' This code sets up the variables needed for performing calculations
' and for actually drawing lines and text on the chart.

Dim average as Double
Dim total as Double
Dim number as Double
Dim s as chSeries
Dim p as chPoint
Dim x as Integer
Dim y as Integer
Dim pa as chPlotArea

' This code cycles through all points in all series on the chart,
' totals the vales, and counts the points. Then the code calculates
' the average value of a point.
Set pa = Me.ChartSpace.Charts(0).PlotArea
For Each s In Me.ChartSpace.Charts(0).SeriesCollection
    For Each p In s.Points
        total = total + p.GetValue(chDimValues)
        number = number + 1
    Next
Next
Average = total/number

' This code uses the series ValueToPoint method to convert the average value
' to a pixel location on the chart.
 Me.ChartSpace.Charts(0).SeriesCollection(0).ValueToPoint x, y, 0, average

' This code sets properties on the drawObject (in this case, the chart).
drawObject.Line.Color = "red"
drawObject.Line.Weight = 2
drawObject.Font.Color = "red"
drawObject.Font.Size = 9

' This code uses the DrawLine and DrawText methods to draw a line and some
' text on the chart. Other available draw methods can provide many
' interesting draw capabilities.
drawObject.DrawLine pa.Left + 1, y, pa.Right - 1, y
drawObject.DrawText "Average", pa.left - 45, y - 10