EXCEL VBA

Excelのマクロ(VBA)でHyperlinkの一括変更

投稿日:

はじめに

EXCELの表を作成して別のEXCELブックやPDFファイルなどをリンク(ハイパーリンク)することが時々あるかと思います。

※売上一覧表に請求書とか、顧客一覧の名刺画像とか

そのリンクしたファイルが自分のパソコンでは無くサーバなどで共有されている場合、ファイルの保存場所をサーバからNASに変更したりすると、EXCEL表のハイパーリンクが機能しなくなります。

件数が少なければ手作業でも良いのですが、大量にあると泣きそうになると思います。

リンクファイルの保存方法に一定の規則があることが前提にはなりますが、マクロ(VBA)機能で一括変換できます。

手順

※開発タブが表示されていない場合は、こちらのリンクを参照して表示させておくと便利です。

以下の表で手順を説明します。C列にリンクがありますのでこれを一括で修正します。2行目から11行目までが修正の対象です。

(1)リンクの内容を確認します。

リンクとなっている資料を右クリックして「ハイパーリンクの編集」をクリックします。

(2)アドレス欄を確認します。今回はSV01をNAS01に変更します。

キャンセルをクリックします。

(3)開発タブから「マクロ」をクリックします。

(4)マクロ画面が表示されます。

(5)マクロ名に「リンク一括変更」と入力して作成ボタンをクリックします。

(6)Visual BASIC for Applications画面(VBA画面)が表示されます。

(7)Sub リンク一括変更() と End Sub の間に次のコードを貼り付けます

    Dim rowAddr As String
    Dim intStart As Integer
    Dim intEnd As Integer
    Dim oldStr As String
    Dim newStr As String
    Dim i As Integer
    Dim buf As String
    
    On Error Resume Next
    
    ' リンクの列を設定します
    rowAddr = "C"
    
    ' リンクの行の開始と終了を設定します
    intStart = 2
    intEnd = 11
    
    ' 変更前のサーバ名と新しいサーバ名(NAS名)を設定します
    oldStr = "\\SV01\"
    newStr = "\\NAS01\"
    
    ' シート名を変更している場合は以下のSheet1を修正します。
    Worksheets("Sheet1").Activate
    
    For i = intStart To intEnd
        ' リンクのセルを選択します
        Range(rowAddr & Trim(Str(i))).Select
        ' リンクのアドレスを取り出します
        buf = Selection.Hyperlinks(1).Address
        ' リンクのアドレスに変更前のサーバ名が含まれているかチェックします
        If InStr(1, buf, oldStr) > 0 Then
            ' サーバ名を変更してリンクのセルを更新します
            Selection.Hyperlinks(1).Address = Replace(buf, oldStr, newStr)
        End If
    Next

    MsgBox "完了しました"

 

(8)以下が貼り付けた画面となります。

※リンクの列・リンクの行範囲・変更前後のアドレス・シート名が修正対象です。コードのコメントを参照しながらご自身のEXCELにあわせて変更してください。

(9)コードを貼り付けて修正したら名前を付けて保存します。このときファイルの種類を”エクセルマクロ有効ブック(*.xlsm)”で保存します。

(10)EXCEL表に戻り、開発タブから「マクロ」をクリックします。

(11)リンク一括変更が選択されている状態で「実行」ボタンをクリックします。

(12)処理が完了するとメッセージが表示されるのでOKをクリックします。

(13)(1)の手順でハイパーリンクの編集をクリックすると以下の画面が表示されますのでアドレス部分が変更できているか確認してください。

終わりに

一括修正を実施する前に元のEXCELファイルは必ずバックアップしておきましょう。間違って壊してしまったらリンクの編集どころではありませんので十分に注意してください。

またご自身の責任において実行してください。私は責任とれませんのでよろしくお願いします。

スポンサードリンク

スポンサードリンク

-EXCEL, VBA
-, , ,

Copyright© あきらちんの技術メモ , 2024 All Rights Reserved Powered by STINGER.