使用 Access 2007 VBA 创建数据透视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/748274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivot Chart Creation Using Access 2007 VBA
提问by Nick Sinas
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.
好吧，互联网上绝对没有好的文章可以解释或提供有关如何使用 VBA 创建数据透视图的代码示例。我需要这个，因为我需要数据透视图根据表单中的用户选择显示不同的结果。Excel 有一些，但 Access 的语法不同。我知道这是一个蹩脚的问题，但如果有人有如何在 VBA 中创建数据透视图的示例，我将非常感谢您的帮助。
采纳答案by Nick Sinas
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.
经过大约 3 天的搜索，我想我找到了。并不是说有人真正关心，这只有 6 次观看，这说明了 VBA 的绝对可怕性。无论如何，MSDN 将其隐藏在“Office XP”下而不是 Access 下，但无论如何。
回答by Mark Plumpton
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).
我在 Access 2007 中创建了一个数据透视图表单。在另一个表单上，我将用户选择控件和数据透视表作为子表单。然后我在主窗体中使用这样的代码。对象模型与 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
常数可以从 OWC11 导出
C:\Program Files\Common Files\Microsoft Shared\Web Components\11\OWC11.DLL
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.
您需要在模块顶部使用它们。在这个阶段，我不确定如何从 Access 数据透视图中提取它们。引用 OWC11 并将子窗体 ChartSpace 设置为声明为 OWC11.ChartSpace 的变量。编写代码后更改为“Object”类型，并删除后期绑定的引用（并重新测试）。这样，当您部署时，您的 refs 不会在 64 位机器上卡住。
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.)
（注意 - 这对我来说仍然是一个新发现，所以如果我发现任何问题，我会努力更新这个答案。）
