トップ > VBA・VBS > Excel VBA > Webページのタイトルと見出しをシートに取り込む

Webページのタイトルと見出しをシートに取り込む

Excel VBA(URLリストから一括取得・間隔を空けてアクセス)

参考ページの一覧を作るときや、公開した記事のタイトルをまとめて確認するとき。ページを1件ずつ開いて、タイトルをコピーして、シートに貼って…という作業は、件数が増えるほど時間がかかります。このレシピでは、シートに並べたURLリストをもとに、各ページのタイトルと最初の見出し(h2)を自動で取り込むマクロを作ります。相手のサーバーに負担をかけないよう、1件ごとに間隔を空けてアクセスする作りにしてあります。取り込む前に、対象サイトの利用規約の確認も必ずお願いします(下の「つまずきどころ」参照)。

✅ これができます:URLのリストから、各ページのタイトルと最初の見出し(h2)をシートに一括で取り込めるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

実行前(左)はURLだけが並んだリスト。実行すると(右)、URLの右隣に各ページのタイトルと最初の見出し(h2)が入ります。取れなかった行には「取得できませんでした」と入ります

これをコピペすれば動きます

Option Explicit
 
' ===== 設定エリア:変えるのはここだけ =====
' ① 対象URLが並ぶシート名
Const URL_LIST_SHEET As String = "URLリスト"
' ② URLが入っている列の番号(A列=1、B列=2…)
Const URL_COL As Long = 1
' ③ アクセスの間隔(秒)。相手のサーバーに負担をかけないため、短くしすぎないこと
Const WAIT_SECONDS As Long = 2
' ===== ここから下は変えなくてOK =====
 
Sub Web情報を取り込む()
    Dim ws As Worksheet
    Dim http As Object
    Dim lastRow As Long, r As Long
    Dim cnt As Long, fail As Long
    Dim url As String, html As String
    Dim ttl As String, h2 As String
 
    Set ws = ThisWorkbook.Worksheets(URL_LIST_SHEET)
    lastRow = ws.Cells(ws.Rows.Count, URL_COL).End(xlUp).Row
 
    ' 書き出す列の見出し(URLの右隣=タイトル、その右=見出し)
    ws.Cells(1, URL_COL + 1).Value = "タイトル"
    ws.Cells(1, URL_COL + 2).Value = "見出し(h2)"
 
    cnt = 0
    fail = 0
    For r = 2 To lastRow
        url = Trim(CStr(ws.Cells(r, URL_COL).Value))
        If url <> "" Then
            ttl = ""
            h2 = ""
            Set http = CreateObject("MSXML2.XMLHTTP.6.0")
            On Error Resume Next
            http.Open "GET", url, False
            http.send
            If Err.Number <> 0 Or http.Status <> 200 Then
                ws.Cells(r, URL_COL + 1).Value = "取得できませんでした"
                ws.Cells(r, URL_COL + 2).Value = "取得できませんでした"
                fail = fail + 1
            Else
                html = http.responseText
                ttl = タグの中身を取り出す(html, "title")
                h2 = タグの中身を取り出す(html, "h2")
                ws.Cells(r, URL_COL + 1).Value = ttl
                ws.Cells(r, URL_COL + 2).Value = h2
                cnt = cnt + 1
            End If
            On Error GoTo 0
            Set http = Nothing
            ' 相手のサーバーに負担をかけないよう、1件ごとに間隔を空ける
            If r < lastRow And WAIT_SECONDS > 0 Then
                Application.Wait Now + TimeSerial(0, 0, WAIT_SECONDS)
            End If
        End If
    Next r
    ws.Activate
    MsgBox "取り込みが終わりました(成功 " & cnt & " 件/取得できませんでした " & fail & " 件)。", vbInformation, "Web情報の取り込み"
End Sub
 
Private Function タグの中身を取り出す(ByVal html As String, ByVal tagName As String) As String
    Dim startPos As Long, tagEnd As Long, endPos As Long
    Dim inner As String
    タグの中身を取り出す = ""
    startPos = InStr(1, html, "<" & tagName, vbTextCompare)
    If startPos = 0 Then Exit Function
    tagEnd = InStr(startPos, html, ">")
    If tagEnd = 0 Then Exit Function
    endPos = InStr(tagEnd, html, "</" & tagName & ">", vbTextCompare)
    If endPos = 0 Then Exit Function
    inner = Mid(html, tagEnd + 1, endPos - tagEnd - 1)
    タグの中身を取り出す = Trim(タグを取り除く(inner))
End Function
 
Private Function タグを取り除く(ByVal txt As String) As String
    Dim i As Long, ch As String, inTag As Boolean, result As String
    result = ""
    inTag = False
    For i = 1 To Len(txt)
        ch = Mid(txt, i, 1)
        If ch = "<" Then
            inTag = True
        ElseIf ch = ">" Then
            inTag = False
        ElseIf Not inTag Then
            result = result & ch
        End If
    Next i
    タグを取り除く = result
End Function

コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

変えるのはここだけ

変える場所を示した図
変えるのは設定エリアの3か所だけ。番号は下の表と対応しています
番号変える場所意味書き方・例
1URL_LIST_SHEET対象URLが並ぶシート名1行目を見出しにして、2行目からURLを並べたシートの名前に書き換えます。例:"URLリスト"
2URL_COLURLが入っている列の番号URLが A列なら 1、B列なら 2。タイトルはその右隣の列に、見出しはさらにその右隣の列に書き出されます
3WAIT_SECONDSアクセスの間隔(秒数)1件取得するごとに空ける待ち時間です。初期値は 2(秒)。相手のサーバーへの負担を考え、0 や極端に短い値にはしないでください。件数が多いときは 3〜5 に広げるのがおすすめです

組み込み手順

  1. URLリストのシートを用意します(シート名は①の値に合わせ、1行目=見出し、2行目以降にURLを1行に1件ずつ並べます)
  2. 取り込む対象のサイトについて、先に利用規約を確認します。自動取得を禁止しているサイトのURLはリストに入れないでください(下の「つまずきどころ」参照)
  3. Alt+F11 でVBAエディタを開き、「挿入」→「標準モジュール」を選びます
  4. このページのコードを貼り付け、設定エリア①〜③を自分のブックに書き換えます
  5. ファイルを .xlsm で保存します
  6. まずはURLを2〜3件だけにして試し実行し、右隣の列にタイトルと見出しが入ることを確認します
  7. 問題なければURLを本番分に戻して実行します。件数が多いほど、③の間隔の分だけ時間がかかります

⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。

⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。

⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。

⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。

⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。

動かし方

VBAエディタの実行ボタン(▶)か、シートに置いたボタンから実行します(ボタンの作り方は「ボタンを置いてマクロを実行する」参照)。実行すると、URLの右隣の列にタイトル、その右隣に最初の見出し(h2)が入ります。取得できなかった行には「取得できませんでした」と入るので、後からその行だけ確認できます。

カスタマイズ例

見出しを h1 や h3 で取りたい

本体の後半で呼び出している タグの中身を取り出す(html, "h2") の "h2" を "h1" や "h3" に変えると、取り出す見出しの種類が変わります。書き出し先の見出し文字(タイトル/見出し(h2))も合わせて直してください。

タイトルだけ取りたい

見出しの取り出しと書き出しの2行を消せば、タイトルだけの取り込みになります。逆に、説明文(meta description)など別の要素を取りたい場合は、同じ関数でタグ名や探し方を応用できますが、ページごとに作りが違うため、まずはタイトルと見出しで運用するのがおすすめです。

URLリストを別の列から始めたい

②の列番号を変えるだけです。書き出し先も自動で「その列の右隣・さらに右隣」に付いていきます。リストの右側にメモなどを書いている場合は、書き出しで上書きされないよう、空いている列から始めてください。

つまずきどころ

関連レシピ

次はこれ:Webページの表を、コードなしでExcelに取り込む(Power Query版)
取りたいものが「ページの中の表」なら、コードを書かずに Excel 標準機能だけで取り込む方法もあります

よくある質問

Q. 参照設定は必要? MSXML2.XMLHTTP60 が無いと言われたら?

A. 参照設定は不要です。コードの中で CreateObject で作る方式(実行時に読み込む方式)のため、そのまま動きます。古い環境などで MSXML2.XMLHTTP.6.0 が使えない場合は、バージョン部分を 3.0 などに下げた書き方が必要になることがありますが、現在の Windows+Excel では通常そのままで動きます。

Q. Macでも使える?

A. このレシピは Windows+デスクトップ版 Excel を前提にしています。Mac版 Excel では、外部への通信に使う部品(MSXML)が用意されていないため、そのままでは動きません。表として取り込めるページであれば、Power Query版(「Webページの表を、コードなしでExcelに取り込む」)も検討してください。

Q. 「取得できませんでした」ばかりになる

A. URLの書き間違い、ログインが必要なページ、JavaScriptで描画されるページ、のどれかが多いです。まずブラウザでそのURLがログインなしで開けるか確認してください。サイトの利用規約で自動取得が禁止されていないかの確認も、合わせて行ってください。

Q. 何件まで一度に取れる?

A. コード上の上限はありませんが、件数が増えるほど相手のサーバーへのアクセスも増えます。数十件単位で区切って実行し、③の間隔も確保してください。大量のURLを短時間に処理する使い方はしないでください。

PR(広告)

もっと体系的に学びたい人へ
自動化の幅を広げるなら、講座で体系化するのが近道です。

Udemy:Excelショートカット講座を見る