PR
セルの操作

【ExcelVBA】ハイパーリンクのリンク先を文字列として出力する

Excelファイル内にある「ハイパーリンク」からリンク先を文字列として出力する場合、条件によってはHYPERLINK関数では出力ができません。

本記事ではExcelVBAを利用してハイパーリンクからリンク先の文字列を出力する方法を紹介します。

ハイパーリンクの種類について

まず、ハイパーリンクの表現としては2種類（①Hyperlinksオブジェクト、②HYPERLINK関数）ありますが、見た目やリンク先へのジャンプ方法（左クリック）の差異はありません。

①Hyperlinksオブジェクト

右クリックメニューの「リンク」を選択し、「アドレス」と「表示文字列」を指定するとハイパーリンクが挿入できます。

②HYPERLINK関数

HYPERLINK関数へ「リンク先(アドレス)」「別名(表示する文字列)」を指定するとハイパーリンクが挿入できます。

リンク先の情報をHYPERLINK関数を使用して出力する

HYPERLINK関数の「リンク先」の参照を出力したいハイパーリンクの「リンク先」にすることで、リンク先のアドレスを文字列として出力することができますが、「別名(表示文字列)」がアドレスと異なる場合は「別名(表示文字列)」が出力されてしまいます。

もし、「別名(表示文字列)」がリンク先と同じ、もしくは省略されている場合はHYPERLINK関数でのリンク先の出力は対応可能です。

リンク先の情報をRangeオブジェクトのプロパティから出力する

HYPERLINK関数ではリンク先の文字列出力対応は不完全であることが確認されましたので、対象のセルの情報を取得することが目的ですので、Rangeオブジェクトを利用することで対応ができないかを考えます。

Sub ハイパーリンク_Hyperlinks()

Dim Range_Link As Range


Set Range_Link = Range("A7")

Debug.Print Range_Link.Hyperlinks.Item(1).Address

End Sub

HyperlinksのAddressプロパティからリンク先の出力は可能ですが、HYPERLINK関数を使用しているセルはHyperlinksとして扱われていないため、リンク先の出力はできません。

Sub ハイパーリンク_Value()

Dim Range_Link As Range


Set Range_Link = Range("A7")

Debug.Print Range_Link.Value

End Sub

また、RangeオブジェクトのValueプロパティからセルに表示されている値を出力することはできますが、この場合も「別名（表示文字列）」を出力してしまうため、リンク先と別名が異なる場合はリンク先の出力はできません。

このように、HYPERLINK関数が使用されており、かつ「リンク先と別名が異なる場合」以外はRangeオブジェクトのプロパティからリンク先が出力できることが確認できました。

リンク先の情報をRangeオブジェクトのプロパティを編集することで出力する

HYPERLINK関数が使用されており、かつ「リンク先と別名が異なる場合」についてですが、HYPERLINK関数内には「リンク先」が入力されていることは確実ですので、Rangeオブジェクトの「Formula（計算式）」プロパティからリンク先を抽出し文字列として出力します。

１）RangeオブジェクトのValueプロパティがFormulaプロパティの13文字目～と同じ場合はValueプロパティをそのままリンク先として出力して問題ありません

２）ValueプロパティとFormulaプロパティが異なる場合はMid関数を使用してリンク先の文字列を出力します

Sub ハイパーリンク_ワークシート関数()

Dim Range_Link As Range
Dim 別名 As String
Dim リンク先 As String
Dim リンク先開始文字数 As Long
Dim リンク先終了文字数 As Long


Set Range_Link = Range("A5")


If WorksheetFunction.Find(Range_Link.Value, Range_Link.Formula) = 13 Then
    
    Debug.Print Range_Link.Value
    
Else

    リンク先開始文字数 = WorksheetFunction.Find("HYPERLINK(""", Range_Link.Formula) + 11
    
    リンク先終了文字数 = WorksheetFunction.Find(""",""" & Range_Link.Value & """)", Range_Link.Formula)

    リンク先 = Mid(Range_Link.Formula, _
               リンク先開始文字数, _
               リンク先終了文字数 - リンク先開始文字数)


    Debug.Print リンク先

End If


End Sub

ハイパーリンクに合わせてリンク先の情報を出力する

それぞれのハイパーリンクに対して、リンク先を出力する方法は分かりましたので整理します。

このように、ハイパーリンクの条件に応じてリンク先の情報を出力する方法が異なりますが、セルの見た目での判別ができないことを考慮して、ハイパーリンクの条件に応じて出力方法を変更できるようにコードをまとめましたので紹介します。

Sub ハイパーリンク_アドレス出力()

Dim Range_Link As Range


Set Range_Link = Range("A2")


'Hyperlinksが存在しているかCountプロパティで確認する
If Range_Link.Hyperlinks.Count = 1 Then

    Debug.Print Range_Link.Hyperlinks.Item(1).Address
    
    Exit Sub

Else 'Count=0の場合

    If Left(Range_Link.Formula, 10) = "=HYPERLINK" Then

        If WorksheetFunction.Find(Range_Link.Value, Range_Link.Formula) = 13 Then
    
            Debug.Print Range_Link.Value
    
        Else

            リンク先開始文字数 = WorksheetFunction.Find("HYPERLINK(""", Range_Link.Formula) + 11
    
            リンク先終了文字数 = WorksheetFunction.Find(""",""" & Range_Link.Value & """)", Range_Link.Formula)

            リンク先 = Mid(Range_Link.Formula, リンク先開始文字数, リンク先終了文字数 - リンク先開始文字数)


            Debug.Print リンク先

        End If

    Else
        'Hyperlinks、HYPERLINK関数が見つからない場合は何もしない
    End If

End If


End Sub

マクロを実行することで、指定したセルのリンク先を出力することができます。

コメント