访问 VBA：将表导出到 Excel 2010 数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16225005/
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
Access VBA: Export table to Excel 2010 pivot table
提问by duney
I currently have the code below which saves two data tables into an Excel workbook on two separate sheets, and then opens the workbook:
我目前有下面的代码，它将两个数据表保存到两个单独工作表上的 Excel 工作簿中，然后打开工作簿：
Dim outputFileName As String
Dim outputFile As String
outputFile = "Export_" & Format(Date, "yyyyMMdd") & Format(Time, "_hhmm") & ".xlsx"
outputFileName = CurrentProject.Path & "\" & outputFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Callouts", outputFileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Members", outputFileName, True
'Load Excel
Set appExcel = New Excel.Application
Set wbkOutput = appExcel.Workbooks.Open(outputFileName)
'Show excel window
appExcel.Visible = True
I'd like to add some code to create a pivot table to display the data in excel but I'm unsure on how to do this - could anyone help please? :-)
我想添加一些代码来创建一个数据透视表以在 excel 中显示数据，但我不确定如何做到这一点 - 任何人都可以帮忙吗？:-)
回答by Barranka
Here is a sample piece of code that creates a pivot table:
下面是一段创建数据透视表的示例代码：
...
Dim aSheet as Worksheet
Set aSheet = ThisWorkbook.Sheets.Add
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="Sheet1!A1:D1000",
Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:=aSheet.Name&"!A1", _
TableName:="YourPivotTable", _
DefaultVersion:=xlPivotTableVersion12
ThisWorkbook.Sheets(aSheet.Name).Select
' Add the column field (the name of the pivot field is one of your data table) '
With ActiveSheet.PivotTables("YourPivotTable").PivotFields("columnHeaderField")
.Orientation = xlColumnField
.Position = 1
End With
' Add the row field (the name of the pivot field is one of your data table) '
With ActiveSheet.PivotTables("YourPivotTable").PivotFields("rowHeaderField")
.Orientation = xlRowField
.Position = 1
End With
' Add the data field
With ActiveSheet.PivotTables("YourPivotTable")
.AddDataField .PivotFields("aValueField"), "Sum of a value", xlSum
.AddDataField .PivotFields("anotherValueField"), "Sum of another value", xlSum
End With
...
Hope this helps you
希望这对你有帮助
回答by Fable
Not sure if you finally find it out, however, in my code, if I use position and xlrow/column field nothing happens either. I got rid off them and used only .orientation = 1 for xlcolumnfield, 2 for xlrowfield, 3 for filtering and 4 to AddDataField. I didn't declare the position, the Excel itself adjust the position based on the order I wrote the code. Said that, I'm instead stuck with the formula: I'm unable to change it from count to sum; I tried with just renaming the value and seems working, since in the field list it shows "Sum of..." but in the pivot itself I still get the count...
不确定您是否最终找到了它，但是，在我的代码中，如果我使用 position 和 xlrow/column 字段，也不会发生任何事情。我摆脱了它们，只使用 .orientation = 1 表示 xlcolumnfield，2 表示 xlrowfield，3 表示过滤，4 表示 AddDataField。我没有声明位置，Excel本身根据我编写代码的顺序调整位置。就是说，我坚持使用公式：我无法将其从计数更改为总和；我尝试只重命名该值并且似乎有效，因为在字段列表中它显示“总和...”但在数据透视本身中我仍然得到计数...
