实用！Excel批量替换数据源路径

在编制月度的合并报表工作底稿时，每个月都需要在工作底稿中编辑链接并更改源，这样底稿中的数据才会更新到最新的期间，但是如果子公司数量较多，也就工作底稿中引用的外部工作簿很多时，这样更改源是非常耗时的，而且容易选错。

在碰到这种引用大量外部工作簿的表格文件需要更改源的情况时，我们还可以尝试另外两种更高效的方式。

批量替换（Ctrl+H）

这种方式适用于公式链接只需要一次替换的情形，如下图所示，只需要将「6月指标」替换为「7月指标」，就可以完成公式链接的替换了，使用时我们需要注意对比新旧表格所在的文件路径。

以下就是替换后的效果，需要注意的是替换之前建议先备份复制下文件。

Ctrl+H 只适用于单次替换的情况，但是实际情况中，如果遇到被引用表格的文件夹位置、工作簿名称、工作表名称、工作簿格式等多处出现改变时，就需要用到VBA来实现批量更改源的需求了。

比如下图中，文件路径中需要替换的字符的有两处，一是需要将文件夹路径进行替换，二是工作簿名称也需要替换。

我们插入VBA代码后，同时将代码中红框的部分进行对应的修改，运行代码后就能实现需求了，如果你的表格中引用外部工作簿的单位格较多，那么程序运行的时间就会稍微长一点。

具体代码如下：

Sub BatchReplaceWorkbookPath()
' 使用前请先备份报表！
' 使用前请先备份报表！
' 使用前请先备份报表！

Dim ws As Worksheet, cell As Range
Dim oldrep1$, oldrep2$, oldrep3$, cf$, newrep1$, newrep2$, newrep3$

oldrep1 = "Excel批量修改源文件链接（替换多次）\6月指标" ' 被替换字符1
newrep1 = "7月指标" ' 替换后字符1
oldrep2 = "6月主要经济指标" ' 被替换字符2
newrep2 = "7月主要经济指标" ' 替换后字符2
oldrep3 = "" ' 被替换字符3
newrep3 = "" ' 替换后字符3

Application.DisplayAlerts = False ' 禁止显示警告框
For Each ws In ActiveWorkbook.Worksheets ' 循环处理工作簿的所有工作表
For Each cell In ws.UsedRange ' 遍历工作表中的所有单元格
cf = cell.Formula
If InStr(cf, oldrep1) > 0 Or InStr(cf, oldrep2) > 0 Or InStr(cf, oldrep3) > 0 Then ' 检查单元格中的公式链接是否包含需替换的字符
cell.Formula = Replace(cell.Formula, oldrep1, newrep1) ' 替换第1个字符
cell.Formula = Replace(cell.Formula, oldrep2, newrep2) ' 替换第2个字符
cell.Formula = Replace(cell.Formula, oldrep3, newrep3) ' 替换第3个字符
End If
Next cell
Next ws
Application.DisplayAlerts = True ' 恢复显示警告框
End Sub
AI的辅助作用

我在AI问答中也对上述需求进行了提问，我现在也习惯了将问题丢给AI，我再根据AI的回答进行修正，这次我同时测试了ChatGPT和文心一言的效果，发现基本是满足要求的。

下面是ChatGPT的效果：

下面是文心一言的效果：

先到这里~~

温馨提示：加入审友交流群/转载/投稿请联系：审家小编 shenjizhijia1。