It only takes one shot! How to bulk replace destination addresses of hyperlinks in Excel workbook

If you want to replace the destination addresses of multiple hyperlinks in an Excel workbook at once, it would be troublesome to change them one by one manually. In such a case, batch replacement can be efficiently performed by utilizing VBA. This article will show you how to bulk replace hyperlink destination addresses in Excel workbook using VBA.

Prepare VBA code

You can use the below VBA code to bulk replace the destination addresses of hyperlinks in an Excel workbook.

Sub ReplaceHyperlinkAddressesInExcel()
    Dim ws As Worksheet
    Dim hLink As Hyperlink
    Dim oldAddressPart As String
    Dim newAddressPart As String

    ' 検索文字列の入力を求める
    oldAddressPart = InputBox("検索文字列を入力してください（例：http://）", "検索文字列")
    If oldAddressPart = "" Then Exit Sub

    ' 置換文字列の入力を求める
    newAddressPart = InputBox("置換文字列を入力してください（例：https://）", "置換文字列")

    ' ワークシートごとにループ
    For Each ws In ActiveWorkbook.Worksheets
        ' ハイパーリンクごとにループ
        For Each hLink In ws.Hyperlinks
            ' ハイパーリンクのアドレスを置換
            If InStr(1, hLink.Address, oldAddressPart) > 0 Then
                hLink.Address = Replace(hLink.Address, oldAddressPart, newAddressPart)
            End If
        Next hLink
    Next ws

    MsgBox "ハイパーリンクのリンク先アドレスの置換が完了しました。", vbInformation, "置換完了"
End Sub

This code will bulk replace hyperlink destination addresses in all worksheets in Excel workbook. The operation procedure is as follows.

Step 1: Open VBA Editor

Open the VBA editor in Excel. To open the editor, press the “Alt” and “F11” keys at the same time.

Step 2: Paste the VBA code

In the VBA editor, choose “Standard Module” from the “Insert” menu, and when the new module opens, paste the previous VBA code. After pasting, close the VBA editor and return to the worksheet.

Step 3: Run the VBA code

To run the VBA code, press Alt and F8 keys simultaneously on the worksheet in Excel to bring up the macro dialog box. Select the macro called ReplaceHyperlinkAddressesInExcel from the dialog box and click the Run button.

Step 4: Enter search and replace strings

When prompted for a search string, enter the part of the destination address you want to replace (for example, http://). After that, a replacement string input dialog will appear, where you can enter part of the new destination address (e.g. https://).

When the typing is done, the VBA code will run to bulk replace the destination addresses of all hyperlinks in the Excel workbook. Finally, you will see the message “Hyperlink destination address replacement completed”, so click the “OK” button to complete.

Summary

With the VBA code introduced here, you can easily bulk replace hyperlink destination addresses in Excel workbooks. Please try to use this method to improve work efficiency.

この投稿文は次の言語で読めます: 日本語 (Japanese) 简体中文 (Chinese (Simplified))