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

王文铖2024-02-212024-03-16

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

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

批量替换（Ctrl+H）

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

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

Excel VBA（多次替换）

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

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

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

具体代码如下：

Basic

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