从 Vba 中的数据透视表中检索数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5141433/
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
Retrieve data from Pivot Table in Vba
提问by Sarika.S
I have an excel work sheet which contains three pivot tables having Pivot Table Names as PivotTable1...PivotTable3 and Active field names as Country, Language and Printers respectively. What I need is to get all data in each pivot table to each string or string array. Any help will be very thankful.
我有一个 excel 工作表，其中包含三个数据透视表，数据透视表名称分别为 PivotTable1...PivotTable3 和活动字段名称分别为 Country、Language 和 Printers。我需要的是将每个数据透视表中的所有数据获取到每个字符串或字符串数组。任何帮助将不胜感激。
采纳答案by MikeD
A quick & dirty one to get you going; all cells of a Pivot table in one linear string, seperated by ";". This should give enough inspiration on which methods and properties to use. Beware: Tmpcannot hold indefinitely large pivot tables, if they grow enormously large, consider writing Tmpto a file instead.
一个快速而肮脏的让你去；一个线性字符串中的数据透视表的所有单元格，用“;”分隔。这应该为使用哪些方法和属性提供足够的灵感。当心：Tmp不能无限期地容纳大数据透视表，如果它们变得非常大，请考虑写入Tmp文件。
Sub PTTest()
Dim SH As Worksheet ' the current worksheet from the colection of workbooks
Dim PT As PivotTable ' the current pivot table from the current worksheet
Dim PTC As Range ' the cell range of the current pivot table
Dim Tmp As String ' the buffer for concatenated cell values
Tmp = ""
' process all sheets, as Pivot table objects are contained by sheets
For Each SH In ActiveWorkbook.Worksheets
For Each PT In SH.PivotTables
For Each PTC In PT.TableRange1.Cells
' all cells in one buffer, seperated by ";"
' if you want to include page header cells, use
' "PT.TableRange2.Cells" instead
Tmp = Tmp & PTC & ";"
Next PTC
' *** do something *** with the buffer
' ok very simple we print it into the debugger's Immediate window
Debug.Print Tmp
' empty buffer for next pivot table
Tmp = ""
Next PT
Next SH
End Sub
Hope that helps .... good luck MikeD
希望有帮助....祝你好运 MikeD
