ハイパーリンクからURLを取得するには
Excelではセルや画像にハイパーリンクを設定することが出来ます。便利な機能ですが、表示文字列とリンクは別で扱われるため、URLやアドレスが直接見えないという欠点があります。
また、リンク先が変わった場合などでハイパーリンクのアドレスを変更をしないといけない場合に、どのセルや画像にどういうハイパーリンクが設定されているのかを確認しないといけない、という面倒な点があります。そういう場合に一つずつ調べるのは大変です。
そこで以下では、ブックに含まれるハイパーリンクを全て抽出する方法を紹介します。
コード
以下の関数を実行すると、アクティブブックの全シートのセルと画像のハイパーリンクを抜き出し、新規シートに一覧で出力します。
出力する内容は「シート名」、「セルや画像の座標」、「セル or 画像 の種類」、「アドレス」の４つです。
画像の場合の座標は、その画像の左上が含まれているセル座標を出力しています。
ハイパーリンクには、インターネットのURLなどの外部へのリンクを示すAddressプロパティと、ブック内のセルを参照する場合などの内部へのリンクを示すSubAddressプロパティがあります。どちらか設定されている方を出力するようにしています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
Sub ExtractLinks() Dim sht As Worksheet '// ワークシート Dim ar() As String '// ハイパーリンク配列 Dim hLink As Hyperlink '// ハイパーリンク Dim sCellAddress As String '// セル座標 Dim sLinkAddress As String '// リンク先 Dim sType As String '// 種類 Dim s As Variant '// 配列の要素文字列 Dim v As Variant '// 分割 ReDim ar(0) '// アクティブブックの全シートをループ For Each sht In Worksheets '// シート内のハイパーリンクをループ For Each hLink In sht.Hyperlinks '// Range（セル）の場合 If hLink.Type = msoHyperlinkRange Then sCellAddress = hLink.Range.Address(False, False) sType = "セル" '// Shape（画像）の場合 ElseIf hLink.Type = msoHyperlinkShape Then sCellAddress = hLink.Shape.TopLeftCell.Address(False, False) sType = "画像" End If '// 外部リンクが設定されている場合 If hLink.Address <> "" Then '// 外部へのハイパーリンクを取得 sLinkAddress = hLink.Address '// 内部リンクが設定されている場合 Else '// 内部へのハイパーリンクを取得 sLinkAddress = hLink.SubAddress End If '// シート名＋セル座標＋種類＋アドレス ar(UBound(ar)) = sht.Name & vbTab & sCellAddress & vbTab & sType & vbTab & sLinkAddress ReDim Preserve ar(UBound(ar) + 1) Next Next '// 配列に格納済みの場合 If IsEmpty(ar(0)) = False Then '// 余分な領域を削除 ReDim Preserve ar(UBound(ar) - 1) End If '// ブックの一番左に新規シートを追加 Call Worksheets.Add(Before:=Worksheets(1)) '// ハイパーリンクの数ループ For Each s In ar '// TAB文字で分割 v = Split(s, vbTab) '// A列にシート名を出力 ActiveCell.Value = v(0) '// B列に座標を出力 ActiveCell.Offset(0, 1).Value = v(1) '// C列に種類を出力 ActiveCell.Offset(0, 2).Value = v(2) '// D列にアドレスを出力 ActiveCell.Offset(0, 3).Value = v(3) '// 下のセルを選択 ActiveCell.Offset(1, 0).Select Next End Sub |
使い方
ハイパーリンクを調べたいブックを表示して（アクティブにして）、上の関数を実行するだけです。ブックの一番左に新規シートが追加されてハイパーリンクの情報が出力されます。
以下は実行例です。A列がシート名、B列がセルや画像（の左上）の座標、C列がセルか画像か、D列がハイパーリンク先のアドレスです。
