如何更改PivotCache.SourceData指向新表? [英] How do I change a PivotCache.SourceData to point to a new Table?

问题描述

在Excel 2010中，我通过VBA代码将工作表从主工作簿复制到新工作簿，以便每个新工作簿可以分别包含一组针对不同客户端的数据.

In Excel 2010, I am copying, via VBA code, worksheets from a Master workbook to a new workbook so that the individual new workbooks can each contain a set of data for different clients.

某些工作表具有从Access DB提取的数据表和基于该数据集的数据透视表.我的代码将工作表从主表复制到新的客户端工作簿，更新工作表上的数据，然后更新 .PivotCache.SourceData 指向新的工作簿.

Some of the worksheets have tables of data pulled from an Access DB, and PivotTables based on that data set. My code copies the worksheet from the master to the new client workbook, updates the data on the worksheet, then updates the .PivotCache.SourceData to point to the new workbook.

此代码可以很好地更新 .PivotCache.SourceData :

This code was working just fine to update the .PivotCache.SourceData:

If XLClinic.PivotCaches.Count <> PivotCacheCount Then
  Formula = NewSheet.PivotTables(1).PivotCache.SourceData
  If InStr(1, Formula, "'") > 0 Then
    Bracket = InStr(1, Formula, "]")
    Formula = Replace(Formula, "'", "")
    NewSheet.PivotTables(1).PivotCache.SourceData = Right(Formula, _
             Len(Formula) - Bracket + 1)
  End If
End If

然后，我将自己的主人"更改为工作表布局，因此我实际上将其定义为 table ，而不是仅仅包含工作表中的一些数据，而且我手动更新了数据透视表以指向表名(我更改了表名称，使其与数据集一致且相关，而不只是 TableX ).

I then changed my "Master" worksheet layout so that instead of just being some data on the worksheet, I actually defined it as a table, and I've manually updated the PivotTable(s) to point to the table name (I changed the table name to something consistent and relevant to the data set, instead of just TableX).

此代码不再可用于更改数据透视表的 .SourceData .当我尝试执行它时，我得到:

This code no longer work to change the .SourceData for the pivot table. When I attempt to execute it, I get:

运行时错误"1004":

Run-time error '1004':

应用程序定义或对象定义的错误

Application-defined or object-defined error

当数据简单地格式化为一堆单元格时， .SourceData 值如下所示:

When the data is simply formatted as a bunch of cells, the .SourceData value looks like this:

'\\ server \ share \ full path [工作簿名称]工作表名称'！$ A $ 4:$ E $ 240

'\\server\share\full path[Workbook Name]Worksheet Name'!$A$4:$E$240

当我将数据格式更改为表格式时， .SourceData 现在看起来像这样:

When I change the formatting of the data to a table, .SourceData now looks like this:

'\\ server \ share \ full path \ Workbook Name`！TableName

'\\server\share\full path\Workbook Name`!TableName

考虑到这种结构上的变化，我将代码更改为:

With this change in structure in mind, I changed my code to this:

If XLClinic.PivotCaches.Count <> PivotCacheCount Then
  Formula = NewSheet.PivotTables(1).PivotCache.SourceData
  If InStr(1, Formula, "'") > 0 Then
    Bracket = InStr(1, Formula, "!")
    NewSheet.PivotTables(1).PivotCache.SourceData = Right(Formula, Len(Formula) - Bracket)
  End If
End If

不幸的是，我仍然遇到运行时错误'1004'.

Unfortunately, I'm still getting the Run-time error'1004'.

我已经确认目标工作簿中的表与原始表具有相同的名称，没有使用通用名称对其进行重命名(这就是为什么我要在我的主服务器上强制使用特定的名称/表).

I have confirmed that the table in the destination workbook has the same name as the original, it's not getting renamed with a generic name (that's why I'm enforcing a specific name/table on my Master).

推荐答案

这是我最终想到的:

If XLClinic.PivotCaches.Count <> PivotCacheCount Then
  Formula = NewSheet.PivotTables(1).PivotCache.SourceData
  Bracket = InStr(1, Formula, "!")
  Formula = Right(Formula, Len(Formula) - Bracket)
  NewPivotRange = NewSheet.Name & "!" & _
                  NewSheet.ListObjects(Formula).Range.Address(ReferenceStyle:=xlR1C1)
  For Each PT In NewSheet.PivotTables
    PT.ChangePivotCache XLClinic.PivotCaches.Create(SourceType:=xlDatabase, _
                                                    SourceData:=NewPivotRange)
  Next
End If

这样做的缺点是，它为每个单个数据透视表创建了一个新的PivotCache.当工作簿构建完成时，我将遍历其他发现的代码，这些代码将消除所有重复的数据透视缓存，以帮助减小工作簿的大小.

The drawback to this is that it creates a new PivotCache for every single pivot table. When the workbook build is complete, I'll run through some other code I found that will eliminate all the duplicate Pivot Caches to help reduce the size of the workbook.

这篇关于如何更改PivotCache.SourceData指向新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！