【Excel】ファイル移動時のハイパーリンク切れを解決するマクロ

発生した問題

Excelファイルにハイパーリンクを設定していたところ、以下のような状況でハイパーリンクが正常に動作しなくなる問題が発生しました：

問題が発生する状況の例：

Excelファイルを C:\作業フォルダ\ から D:\新しいフォルダ\ に移動
ネットワークドライブから別のPCのローカルフォルダにコピー
USBメモリやクラウドストレージ（OneDrive、Googleドライブなど）に保存
メールで送信して、受信者が異なる場所に保存

問題の詳細

ハイパーリンクは表示上は絶対パス（例：\\server\folder\file.xlsx）を示していました
しかし、Excelの内部では相対パスで記録されることがありました
Excelファイル自体の保存場所が変わると、相対パスの基準点が変わってしまう
結果として、リンクをクリックしても「指定されたファイルが見つかりません」エラーが発生

具体的な症状

元の保存場所（例：C:\作業フォルダ\）では正常に動作するハイパーリンク
新しい場所（例：D:\新プロジェクト\ や USBメモリ、クラウドフォルダ）にファイルを移動後、同じリンクをクリックしても「ファイルが見つかりません」エラー
ハイパーリンクの表示上は正しいパス（\\server\data\file.xlsx）が見えているのに、実際には機能しない

解決策：自動更新マクロの実装

この問題を解決するため、ファイルを開く際に自動的にハイパーリンクのアドレスを再設定するマクロを作成しました。

マクロの動作原理

ファイルを開いた瞬間（Workbook_Openイベント）にマクロが実行
「関連リンク」シートのB列をチェック
特定の条件に合致するハイパーリンクを検出
表示されている絶対パスでハイパーリンクアドレスを再設定

実装したVBAコード

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim h1 As hyperlink
    
    'ブレークのシーンを設定
    Set ws = ThisWorkbook.Sheets("関連リンク")
    
    'B列の最終行を取得（データがある最終行）
    On Error Resume Next
    lastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
    On Error GoTo 0
    
    'B列の2行目から最終行までループ
    If lastRow >= 2 Then
        For i = 2 To lastRow
            On Error Resume Next
            'B列のセルにハイパーリンクが存在するか確認
            For Each h1 In ws.Cells(i, "B").Hyperlinks
                '表示文字列を取得
                Dim displayText As String
                On Error Resume Next
                displayText = h1.TextToDisplay
                On Error GoTo 0
                
                '文字列の表示系を設定（YY または 3文字以上かつ「:Y」を含む場合）
                If Left(displayText, 2) = "YY" Or (Len(displayText) >= 3 And InStr(1, displayText, ":Y") = 2) Then
                    'ハイパーリンクの表示文字列を設定
                    h1.Address = displayText
                End If
            Next h1
        Next i
    End If
End Sub

マクロの仕組み詳細

1. 対象条件の判定

マクロは以下の条件のハイパーリンクのみを更新します：

表示テキストが「YY」で始まる場合
または、3文字以上で2番目の文字が「:」、3番目が「Y」の場合（例：「C:Y...」）

2. 処理の流れ

シート取得：「関連リンク」シートを対象に設定
範囲特定：B列のデータがある最終行まで処理
ループ処理：2行目から最終行まで各セルをチェック
条件判定：上記の条件に合致するハイパーリンクを特定
アドレス更新：表示テキストをそのままアドレスとして再設定

3. エラーハンドリング

On Error Resume Next / On Error GoTo 0 でエラー発生時も処理を継続
ハイパーリンクが存在しないセルでもエラーにならないよう配慮

実装時の注意事項

⚠️ 最重要：VBE（Visual Basic Editor）での配置場所

このマクロは必ず「ThisWorkbook」モジュールに記録してください。

なぜThisWorkbookでないとダメなのか？

Workbook_Open() イベントはThisWorkbookモジュールでしか動作しない
「Module1」「Module2」や「Sheet1」などに書いても全く実行されません
ファイルを開いた瞬間の自動実行は、ThisWorkbookモジュールの特権機能

よくある間違いとその結果

' ❌ Module1に書いた場合（動作しない）
Sub Workbook_Open()  ' Private Sub でも同様
    ' このコードは絶対に実行されない
End Sub

' ❌ Sheet1のモジュールに書いた場合（動作しない）
Private Sub Workbook_Open()
    ' このコードも実行されない
End Sub

' ✅ ThisWorkbookに書いた場合（正常動作）
Private Sub Workbook_Open()
    ' ファイルオープン時に自動実行される
End Sub

VBEでの正しい設定手順

Alt + F11 でVBE（Visual Basic Editor）を開く
左側のプロジェクトエクスプローラーで「ThisWorkbook」をダブルクリック
右側のコードウィンドウにマクロコードを貼り付け
間違っても「Sheet1」や「Module1」には書かない

シート名の調整について

シート名「関連リンク」は変更可能です：

' 実際のシート名に合わせて変更
Set ws = ThisWorkbook.Sheets("Sheet1")        ' 標準シート名
Set ws = ThisWorkbook.Sheets("リンク一覧")    ' カスタムシート名
Set ws = ThisWorkbook.Sheets("ハイパーリンク") ' 任意のシート名

優先順位：配置場所（ThisWorkbook）＞シート名の調整

ファイル形式

マクロ有効ブック（.xlsm）または Excel バイナリブック（.xlsb）で保存
.xlsxではマクロが保存されないため注意

セキュリティ設定

マクロの実行を有効にする必要があります
信頼できる場所にファイルを配置するか、マクロを有効にして開いてください

効果と利点

解決できること

ファイル移動後のハイパーリンク切れを防止
手動でのリンク修正作業が不要
複数のハイパーリンクを一括で自動修正

運用面での改善

ファイル共有時の信頼性向上
メンテナンス作業の大幅な削減
ユーザーの利便性向上

トラブルシューティング

マクロが動作しない場合

マクロの実行が有効になっているか確認
「ThisWorkbook」モジュールにコードが配置されているか確認
シート名「関連リンク」が正確に存在するか確認

特定のハイパーリンクが更新されない場合

表示テキストが条件（「YY」開始または「:Y」パターン）に合致しているか確認
ハイパーリンクが実際にB列に設定されているか確認

まとめ

Excelのハイパーリンクは内部的に相対パスで管理される場合があり、ファイル移動時にリンク切れが発生する可能性があります。今回実装したマクロにより、ファイルを開く度に自動的にハイパーリンクが修復されるため、この問題を根本的に解決できました。

同じような問題に遭遇した際は、このマクロをベースに条件部分を調整することで、様々なパターンのハイパーリンク問題に対応できるでしょう。