基于数据模型构建的透视表分享透视缓存

Question

我只是在整理我的工作簿，我使用了以下代码来合并我的数据透视表缓存（在整理之前大约有200个）。

Sub changeCache()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pc As PivotCache
Dim first As Boolean
On Error Resume Next

    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        For Each pt In ActiveSheet.PivotTables

            If first = False Then
                Set pc = pt.PivotCache
                first = True
            End If 

            pt.CacheIndex = pc.Index

        Next pt
    Next ws

End Sub

这使我的“数据透视缓存”数量减少到33。

Sub CountCaches()
  MsgBox ActiveWorkbook.PivotCaches.Count
End Sub

它为什么是33而不是1是因为我有32个使用数据模型构建的数据透视表。

我的问题是：有人知道如何更改使用数据模型构建的数据透视表以全部使用相同的PivotCache吗？

编辑

我的第二个问题是：所有基于数据模型构建的多个数据透视表

a）引用单个数据模型；还是

b）每个都有自己的模型，因此使Excel文件“膨胀”

编辑2

经过进一步探索，似乎数据模型确实是共享的，适用于引用相同数据的数据透视表。这可以在“连接”中看到（在功能区“数据”选项卡下）。理论上说，即使代码ActiveWorkbook.PivotCaches.Count计算共享连接的每个数据透视表并错误地表示多个缓存，但这不应该使文件“膨胀”。

然而，我将保留悬赏以防有人能够提供更确切的答案。

- Chris

---

请查看此页面上的信息。我尚未测试缓存是否以相同的方式构建，但它可能会让您更接近答案。http://datapigtechnologies.com/blog/index.php/cut-the-size-of-your-pivot-table-workbooks-in-half/ - Lumigraphics

谢谢提供链接，但没有运气。 - Chris

Answer 1

如果我理解您的问题正确，您只需要将每台电脑设置为第一台。因此，第一次传递时，将电脑命名为其他名称，例如pcfirst，然后对于每个剩余的缓存，将pc = pcfirst设置。这里有一些源信息http://www.contextures.com/xlPivot11.html和http://www.mrexcel.com/forum/excel-questions/380933-set-multiple-pivot-cache-read-one-cache.html。

- user3476534

---

谢谢提供这些链接，但不幸的是它们并没有直接回答问题。它们包含了一些关于清理数据透视表缓存的好信息，但似乎使用数据模型构建的数据透视表（即在创建数据透视表时勾选“将此数据添加到数据模型”）并不是基于数据透视表缓存构建的。 - Chris

Answer 2

我还不是很熟悉数据模型，无法提供确切的解释。

但我曾使用以下代码清理我正在工作的一个报告系统，这可能有助于减少PivotCaches：

Sub Caches_Matches()
Dim Ws1 As Worksheet, _
    Pt1 As PivotTable, _
    Ws2 As Worksheet, _
    Pt2 As PivotTable, _
    PcNb As Integer

PcNb = ActiveWorkbook.PivotCaches.Count
MsgBox "PivotCaches.Count = " & PcNb, vbInformation + vbOKOnly, "Before update"

On Error Resume Next

    For Each Ws1 In ActiveWorkbook.Worksheets
        For Each Pt1 In Ws1.PivotTables
            'fix one pt, loop all of them and set same cache if same source
            For Each Ws2 In ActiveWorkbook.Worksheets
                For Each Pt2 In Ws2.PivotTables
                    If Pt1.SourceData <> Pt2.SourceData Or Pt1.PivotCache = Pt2.PivotCache Or Pt1.Name = Pt2.Name Then
                    Else
                        Pt2.CacheIndex = Pt1.PivotCache.Index
                    End If
                Next Pt2
            Next Ws2
        Next Pt1
    Next Ws1

MsgBox "PivotCaches.Count = " & ActiveWorkbook.PivotCaches.Count & Chr(10) & _
        "Before update = " & PcNb, vbInformation + vbOKOnly, "After update"
End Sub

- R3uK

---

谢谢您的回复。不幸的是，您的代码产生的结果与我在原始问题中发布的代码相同。使用数据模型构建的数据透视表仍然报告了多个缓存。 - Chris

我希望能找到更多关于这个主题的资料来工作，但是没有找到太多，希望你能找到一种方法！ - R3uK

谢谢伙计！从我所做的研究来看，虽然使用数据模型构建的表格会报告多个缓存，但这些缓存基本上是空的，并不会使文件变得臃肿（这是我的主要担忧）。 - Chris

谢谢提供的信息，将来可能对我有用！ - R3uK