CODEWITHSUNDEEP
ms-accesschartsvbapivot
Nick Sinas
Nick Sinas

Reputation: 2634

Pivot Chart Creation Using Access 2007 VBA

OK there are absolutely no good articles on the internet that I can find that explain or have code examples on how to create a pivot chart using VBA. I need this because I need the pivot chart to show different results depending on user selection in a form. There are some for Excel but the syntax is different for Access. I know this is a lame question, but if anyone has an example of how to create a pivot chart in VBA I would really appreciate the help.

Upvotes: 5

Views: 14319

Answers (2)

Mark Plumpton
Mark Plumpton

Reputation: 557

I've create a PivotChart form in Access 2007. On another form I have the user selection controls and the pivotform as a subform. Then I use code like this in the main form. The object model is the same as OWC11 (Office Web Components 11).

Private Function DisplayChart()

  With mysubform.Form.ChartSpace
    .Clear
    .AllowFiltering = True
    .HasChartSpaceTitle = True
    .ChartSpaceTitle.Caption = "test"
    .DisplayFieldButtons = False
    .DisplayToolbar = False

    .ConnectionString = ...
    .CommandText = "SELECT rSeries, rCategory, rDate, rValue " & _
        "FROM myTable"

    .Charts(0).Type = chChartTypePie

    .SetData chDimSeriesNames, chDataBound, "rSeries"
    .SetData chDimCategories, chDataBound, "rCategory"
    .SetData chDimValues, chDataBound, "rValue"

    .HasChartSpaceLegend = True

  End With

End Function

the constants can be derived from OWC11

C:\Program Files\Common Files\Microsoft Shared\Web Components\11\OWC11.DLL

You need them at the top of the module. At this stage I'm not sure how to extract them from the Access pivotchart. Make a reference to OWC11 and set the subform ChartSpace to a variable declared as an OWC11.ChartSpace. After writing the code change to type 'Object', and remove the reference for late binding (and re-test). This way your refs won't come unstuck on a 64bit machine when you deploy.

Private Enum ChartConstants
  chDimSeriesNames = 0
  chDimCategories = 1
  chDimValues = 2
  chDataBound = 0
  chAxisPositionValue = -8
  chAxisPositionCategory = -7
  chChartTypePie = 18
End Enum

Remember you can also let the user have access to the PivotChart properties form, field lists and drop zones. Or they can right-click the chart to get to them.

(Note - this is still a new discovery for me so I will endeavor to update this answer if I find any gotcha's.)

Upvotes: 0

Nick Sinas
Nick Sinas

Reputation: 2634

Well after about 3 days of searching I think I found it. Not that anyone really cares, this only has like 6 views, says a lot for VBA's utter horribleness. Anyway, MSDN had this hidden under "Office XP" instead of under Access, but whatever.

http://msdn.microsoft.com/en-us/library/aa662945.aspx#

Upvotes: 3

Related Questions