vba Excel 2010：如何在不断开切片器的情况下更改数据透视表源数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15315146/
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
Excel 2010: How to change pivot table source data without disconnecting slicers?
提问by Morgan Dee
I have researched like mad about this, and I'm worried there isn't an answer. But maybe the really smart people on this site can help.
我对此进行了疯狂的研究，我担心没有答案。但也许这个网站上真正聪明的人可以提供帮助。
I have two workbooks that work together - Charts.xlsm and Data.xlsm. They are always kept together in the same folder. The Charts.xlsm obviously contains all of my charts, but they are all linked to tables in Data.xlsm for their source. I also have lots of slicers in my Charts.xlsm that are connected to the charts, and they share caches when they are connected to charts with the same data source. The two workbooks are always open at the same time so that the data source reference looks like this: 'Data.xlsm'!Table1
我有两个可以协同工作的工作簿 - Charts.xlsm 和 Data.xlsm。它们总是一起保存在同一个文件夹中。Charts.xlsm 显然包含我所有的图表，但它们都链接到 Data.xlsm 中的表作为其来源。我的 Charts.xlsm 中也有很多连接到图表的切片器，当它们连接到具有相同数据源的图表时，它们共享缓存。这两个工作簿始终同时打开，因此数据源引用如下所示：'Data.xlsm'!Table1
This all works great, until I put these workbooks on another computer (which is why I am doing this so I need to find out how to fix this).
这一切都很好，直到我将这些工作簿放在另一台计算机上（这就是我这样做的原因，所以我需要找出如何解决这个问题）。
Once the workbooks are closed, the source data references change to a specific location on my harddrive: 'C:\Folder\Data.xlsm'!Table1
关闭工作簿后，源数据引用更改到我硬盘上的特定位置：'C:\Folder\Data.xlsm'！Table1
If I want to manually change this back to a local reference, I have to first go through and disconnect every single slicer, refresh the tables, then reconnect every slicer. Not a viable solution for my clients.
如果我想手动将其改回本地引用，我必须首先检查并断开每个切片器的连接，刷新表，然后重新连接每个切片器。对于我的客户来说，这不是一个可行的解决方案。
I would use VBA to change the references every time Charts.xlsm is open, but when I tried it one of two things would happen: either the workbook produced errors that would prevent saving, or Excel would crash completely.
每次打开 Charts.xlsm 时，我都会使用 VBA 更改引用，但是当我尝试它时，会发生两种情况之一：工作簿产生的错误会阻止保存，或者 Excel 会完全崩溃。
This is the code that works perfectly for disconnecting the slicers, but produces the 'save' error:
这是用于断开切片器的完美代码，但会产生“保存”错误：
Sub Disconnect_Slicers()
Dim oSliceCache As SlicerCache
Dim PT As PivotTable
Dim i As Long
For Each oSliceCache In ThisWorkbook.SlicerCaches
With ActiveWorkbook.SlicerCaches(oSliceCache.Name).PivotTables
For i = .Count To 1 Step -1
.RemovePivotTable (.Item(i))
Next i
End With
Next oSliceCache
End Sub
So... I am asking the Excel/VBA geniuses out there if there is any way I can maintain a relative location for my charts when they are looking for Data.xlsm so that no matter what computer I open those workbooks on, they will always be actively linked.
所以......我在问 Excel/VBA 天才在他们寻找 Data.xlsm 时是否有任何方法可以为我的图表维护一个相对位置，这样无论我在哪台计算机上打开这些工作簿，他们都会始终保持积极联系。
Thank you SO much in advance!
非常感谢您！
回答by Kazimierz Jawor
If always both files are in the same folder you could possibly go this way.
如果两个文件总是在同一个文件夹中，您可能会采用这种方式。
A. Switch off auto 'UpdateLinks' of Chart.xlsm file. You could do this once manually or, for safety reason, always when BeforeCloseevent fires to avoid some possible problems:
A. 关闭 Chart.xlsm 文件的自动“UpdateLinks”。您可以手动执行一次，或者出于安全原因，始终在BeforeClose事件触发时执行此操作以避免一些可能的问题：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.UpdateLinks = xlUpdateLinksNever
End Sub
B. When you open Chart.xlsm change the link to Data.Xlsm using Workbook Open event + additionally refresh links. In this situation we check path to Chart.Xlsm file and search Data.Xlsm in the same folder. I assume that there is only one link to any other file otherwise some changes could be required:
B. 当您打开 Chart.xlsm 时，使用 Workbook Open 事件 + 额外刷新链接将链接更改为 Data.Xlsm。在这种情况下，我们检查 Chart.Xlsm 文件的路径并在同一文件夹中搜索 Data.Xlsm。我假设只有一个链接到任何其他文件，否则可能需要进行一些更改：
Private Sub Workbook_Open()
'changing first and only one link to new one
Dim a
a = ActiveWorkbook.LinkSources
ThisWorkbook.ChangeLink Name:=a(1), _
NewName:=ThisWorkbook.Path & "\Data.xlsm", Type:=xlExcelLinks
'update the link
ThisWorkbook.UpdateLink Name:=a(1), Type:=xlExcelLinks
End Sub
I admit I do not consider all the risks therefore some test are required.
我承认我没有考虑所有风险，因此需要进行一些测试。
