【手作業からの解放】ExcelのリンクをVBAで一括置換する方法！

毎月更新するExcelファイル、手作業でのリンク修正に追われていませんでしょうか？

・ファイル名を誤って入力して、リンクが切れてしまった。。。
・セルのリンクを直接修正していたら、古いリンクと新しいリンクが混じってしまい、余計な労力がかかってしまった。。。
・フォルダ構成が変わるたび、リンクを手動で開いては修正するのが面倒。。。

Excelで外部ファイルを参照するシートは便利な反面、リンク先の一部が変わると一気にメンテナンスが大変ですよね。

特に複数シートや大量のセルにリンクがある場合、「作業量が多すぎて途方に暮れる。。。」という声も少なくありません。

私も10ファイル以上のリンクがあるExcelファイルを修正することがあるのですが、手作業でリンク編集していると、「リンクの編集を押す」→「リンク先のファイルを探す」→「ファイルを選択したあと、リンク変更を待つ」を延々ループしていまして。。。

たまに無心で作業していると、

リンクするファイル間違えた～（泣）

と悲劇が発生し、やり直す羽目に。。。
ということがちょくちょくありました。。。

この記事では、このようなリンク変更作業を簡単且つ効率的に実行できる解決策を紹介します！

リンク変更でお悩みの方！
ぜひ一度目を通していただけると嬉しいです！

■この記事で解決できること
・文字列の一部を置換して、リンク先を変更
・特定のシートのみ/ブック全体を対象に置換が可能
・セルに指定した置換キーワードをもとに自動処理
・リンクパス全体を指定して、一括置換で参照先を変更

置換に使うVBAマクロ（用途別）
まとめ：面倒なExcelリンク修正、今日で卒業しませんか？

置換に使うVBAマクロ（用途別）

今回は、以下3つのファイルを用意しました。
・「VBA置換」ファイル
・「参照先_No1」ファイル
・「参照先_No2」ファイル

「VBA置換」ファイルに「参照先_No1」ファイルのリンクを貼っているため、それを「参照先_No2」ファイルに置換していきます。

パターン1：特定シートだけのリンクを一括置換（文字列の一部）

「参照先_No1」ファイルの中身はこのようになっています。
セルA1～C1に「参照先_No1」ファイルのリンクを貼っていますので、このリンクを「参照先_No2」に置換します。

今回は「No1」を「No2」に置換すればリンクの貼り替えができるため、セルG2～G3に記載している値を参照し、置換を行っていきます。

セルG2に「旧文字列」、G3に「新文字列」を記載していますので、こちらを参照して置換するVBAを記述します。

Sub ReplaceText_Worksheet()

    Dim ws As Worksheet
    Dim cell As Range
    Dim oldName As String
    Dim newName As String

     ' G2に「置換前の文字」、G3に「置換後の文字」があるとする
     '「Sheet1」は必要に応じてリネームしてください。
    oldName = Sheets("Sheet1").Range("G2").Value
    newName = Sheets("Sheet1").Range("G3").Value

    ' 対象のシート（今回はアクティブシート）
    Set ws = ActiveSheet

    ' ワークシートのUsedRangeをすべてチェック
    For Each cell In ws.UsedRange
        If cell.HasFormula Then
            If InStr(cell.Formula, oldName) > 0 Then
                ' 数式の中に旧ファイル名があれば、新ファイル名に置換
                cell.Formula = Replace(cell.Formula, oldName, newName)
            End If
        End If
    Next cell

    MsgBox "リンクの置換が完了しました！"
    
End Sub

さっそく実行してみましょう！
このVBAを実行すると、、、

「リンクの置換が完了しました！」とメッセージが表示され、参照先が「参照先_No2」に変わりました！

パターン2：ブック全体のリンクを一括置換（文字列の一部）

ちなみにパターン1では「Activesheet」を参照しているため、開いているシートのみしか参照変更できません。

以下のように、同じ内容の「Sheet2」を作成し、「Sheet1」でVBAを実行した場合、、、

ActiveSheetである「Sheet1」は参照先が変更されましたが、「Sheet2」の参照先はそのまま。。。

複数シートの参照先を一気に変更したいときもありますよね。
その場合、以下のVBAコードで実行することができます！

Sub ReplaceText_Workbook()

    Dim ws As Worksheet
    Dim cell As Range
    Dim oldName As String
    Dim newName As String

     ' G2に「置換前の文字」、G3に「置換後の文字」があるとする
    oldName = Sheets("Sheet1").Range("G2").Value
    newName = Sheets("Sheet1").Range("G3").Value

    ' このワークブックのワークシートのUsedRangeをすべてチェック
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If cell.HasFormula Then
                If InStr(cell.Formula, oldName) > 0 Then
                    ' 数式の中に旧ファイル名があれば、新ファイル名に置換
                    cell.Formula = Replace(cell.Formula, oldName, newName)
                End If
            End If
        Next cell
    Next ws
    
    MsgBox "リンクの置換が完了しました！"
    
End Sub

このVBAを実行してみると、、、

「Sheet1」、「Sheet2」いずれの参照先も変えることができました！

パターン3：リンク先のファイルパスを完全に置換（セル指定）

最後に、リンク先のファイル自体を置換するケースを紹介します。

先ほどと同様、セルG2に「旧文字列」、G3に「新文字列」を記載していますので、こちらを参照して置換するVBAを記述します。

現在は「参照先_No1」ブックを参照していますね。

「参照先_No1」ファイルから、「参照先_No2」ファイルにリンクを変更する場合、以下のVBAを実行します。

Sub ReplaceLink_Workbook()

    Dim oldName As String
    Dim newName As String

     ' G2に「置換前のリンク」、G3に「置換後のリンク」があるとする
    oldName = Sheets("Sheet1").Range("G2").Value
    newName = Sheets("Sheet1").Range("G3").Value

    ' ブックにリンクした「oldName」ファイルを「newName」ファイルに変更
    ActiveWorkbook.ChangeLink oldName, newName, xlExcelLinks
    
    MsgBox "リンクの置換が完了しました！"
    
End Sub

このVBAを実行してみると、、、