vba 更改枢轴缓存的简单方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23936355/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-12 03:18:33 来源:igfitidea点击:
Simple way to change a pivot cache
提问by whytheq
This doesn't work...
这不起作用...
Sub changeData_Error()
Dim pc As PivotCache
Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1:B2"))
Excel.Sheets("Sheet1").PivotTables("PivotTable1").ChangePivotCache pc
ThisWorkbook.RefreshAll
End Sub
Have ended up with the following which seems over complicated. Can it be simplified?
最终得到以下似乎过于复杂的内容。可以简化吗？
Sub changeData()
':: this is the table I'd like to change the data
Dim mainP As PivotTable
Set mainP = ThisWorkbook.Sheets("Sheet1").PivotTables("PivotTable1")
':: create a new cache and set it to the new data range
Dim pc As PivotCache
Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range("A1:B3"))
':: create a temporary pivot table
Dim pt As PivotTable
Set pt = ThisWorkbook.Sheets("Sheet1").PivotTables.Add(pc, Range("AA1"), "temptable")
':: find the index of the cache used by the temp table
Dim i As Integer
i = pt.CacheIndex
':: use the index found to redirect the main pivot at the new cache
mainP.CacheIndex = i
':: get rid of temp table
pt.TableRange2.Clear
':: this might not be needed
ThisWorkbook.RefreshAll
End Sub
采纳答案by Pieter Geerkens
The following code switches between two different datasets, easily seen visibly by the presence/absence of Col3in the Field List for the Pivot report:
以下代码在两个不同的数据集之间切换，通过Pivot 报告的字段列表中是否存在Col3可以很容易地看到：
Option Explicit
Public Sub SwitchToData1()
On Error GoTo ErrHandler
SwitchCacheData _
ThisWorkbook.Sheets("Report").PivotTables("PivotTable1"), _
Range("Data1!A1:B4")
EndSub:
Exit Sub
ErrHandler:
MsgBox "Error #" & Err.Number * vbCrLf & Err.Description, _
vbOKOnly Or vbCritical, _
"Error!"
Resume EndSub
End Sub
Public Sub SwitchToData2()
On Error GoTo ErrHandler
SwitchCacheData _
ThisWorkbook.Sheets("Report").PivotTables("PivotTable1"), _
Range("Data2!A1:C4")
EndSub:
Exit Sub
ErrHandler:
MsgBox "Error #" & Err.Number * vbCrLf & Err.Description, _
vbOKOnly Or vbCritical, _
"Error!"
Resume EndSub
End Sub
Private Sub SwitchCacheData(pvt As PivotTable, rng As Range)
pvt.ChangePivotCache ThisWorkbook.PivotCaches.Create(xlDatabase, rng)
pvt.RefreshTable
End Sub
