vba Excel通过VBA创建数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31732871/
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
Excel create Pivot Table by VBA
提问by Shan
I am creating a simple Pivot table by VBA. I don't know what is wrong with my code. My code runs without error but the results is different. Could you please tell me where is the problem in my code.
我正在通过 VBA 创建一个简单的数据透视表。我不知道我的代码有什么问题。我的代码运行没有错误，但结果不同。你能告诉我我的代码中的问题在哪里吗？
Sub CreatePivotTable()
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:B53821").Address(ReferenceStyle:=xlR1C1)
'Create a new worksheet
Set sht = Sheets.Add
'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A1").Address(ReferenceStyle:=xlR1C1)
'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData)
'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTable1")
pvt.PivotFields("Module").Orientation = xlRowField
pvt.PivotFields("KW").Orientation = xlColumnField
pvt.AddDataField pvt.PivotFields("Module"), "Anzahl von Module", xlCount
pvt.PivotFields("Module").PivotFilters.Add Type:=xlTopCount, DataField:=pvt.PivotFields("Anzahl von Module"), Value1:=12
End Sub
If I do it manually, this is the result I get and this is what I want at the end.
如果我手动进行，这就是我得到的结果，这就是我最终想要的。
My code gives me this result. Which is wrong.
我的代码给了我这个结果。这是错误的。
回答by Pedro Polonia
I think your problem is in your small initial range with only two columns (A, B) and the last lines of code
我认为你的问题在你的初始范围很小，只有两列（A，B）和最后几行代码
'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:B53821").Address(ReferenceStyle:=xlR1C1)
Let's suppose you have your name in column A, call Module and column B is KW with a number (the KW/h of your module)
假设您在 A 列中有您的名字，请调用 Module 并且 B 列是带有数字的 KW（模块的 KW/h）
'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="PivotTableTest")
pvt.AddDataField pvt.PivotFields("Module"), "Num Module", xlCount
pvt.PivotFields("Module").Orientation = xlColumnField ' not xlRowField
pvt.PivotFields("KW").Orientation = xlRowField ' not xlColumnField
You don't need the last line.
你不需要最后一行。
回答by macstoll
Try adding the xlRowField and xlColumnField afteryou added the calculated field.
添加计算字段后，尝试添加 xlRowField 和 xlColumnField 。
e.g.
例如
first
第一的
pvt.AddDataField pvt.PivotFields("Module"), "Anzahl von Module", xlCount
then
然后
pvt.PivotFields("Module").Orientation = xlRowField
pvt.PivotFields("KW").Orientation = xlColumnField
