Excel公式在链接到外部工作表并复制时返回#REF

Question

我有一个电子表格October2016.xls，其中包含指向其他工作表的外部链接的公式。当我将工作表复制到其他目录时，我会得到一个#REF错误。例如，我需要链接到Assembly Totals.xls

在我电脑上的原始目录上，我会打开October2016.xls文件，在一个单元格中，我会看到这样的内容：='C:\users\desktop。。。PROD DEPT\PAPERLESS\2016\Oct\Defects[Assembly Totals.xls]！总计'！A1

当复制到其他计算机(或我计算机上的文件位置)时，我会得到以下信息：='C:\users\desktop。。。PROD DEPT\PAPERLESS\2016\Oct\Defects[Assembly Totals.xls]#REF'！A1

我知道，该方案仍在努力走老路。但很明显，当复制到另一个目录时，它会变得混乱。如果我用单词"Total"代替#REF，那么它就很好用——我想这就是相对引用派上用场的地方。然而，问题是Excel希望我从"更新值"窗口中选择公式(Assembly Totals.xls)中的链接电子表格，以到达新保存的路径/文件位置。当你有100多个替代品时，这是乏味的。

我曾尝试编写"查找并替换宏"来更改路径。即，将单词#REF替换为单词Total。我还将旧路径的一部分替换为"空白"，因为该部分路径在新目录路径中不再有效。例如，假设我只复制了\Defects[Assembly Totals.xls]中的文件！总计'！A1.因此，我将删除"PROD DEPT\PAPERLESS\2016\Oct\"，因为新计算机将没有这些文件。

然而，我仍然有数百个更新值窗口，我需要在其中选择文件。

以下是我为消除更新值窗口所做的：-然而，这并不能阻止窗口的出现。

Sub terfuge()
' todo: Assign keystroke shortcut
If Application.DisplayAlerts Then
Application.DisplayAlerts = False
Else
Application.DisplayAlerts = True
End If
End Sub

这是我的查找和替换功能，它运行良好，只是不断出现的更新值窗口

Sub Multi_FindReplace()
Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

fndList = Array("#REF", "PROD DEPTPAPERLESS2016Oct")
rplcList = Array("Total", "")
'Loop through each item in Array lists
For x = LBound(fndList) To UBound(fndList)
'Loop through each worksheet in ActiveWorkbook
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
Next x
End Sub 

谢谢Martin

Answer 1

不是一个完整的答案，但我不会专注于寻找和替换外部电子表格引用来更新您的公式-如果一切设置正确，Excel应该能够在文件路径更改时维护外部电子表格参考。。。查找和替换参考文献是一个变通办法，而变通办法1)通常会失败，2)如果有适当的计划，通常是不必要的。

因此，我将重点关注以下内容：1) 这两个电子表格中的任何一个发生了什么，可能会导致您的电子表格丢失其引用——在引用有效和无效之间，对源电子表格做了什么吗？如果该文件正在手动更新或通过宏更新，而没有适当的注意，则最终可能会删除引用。。

2) 检查你想要做的事情的结构，以及你是如何使用这两个电子表格的——你需要在外部电子表格中有数据吗？您可以编写一个宏，将源文件中的数据复制并粘贴到主电子表格的辅助选项卡中，这样您的所有公式都不会包含外部引用。。。

希望这能有所帮助，SilkCode