【Excel】複数のWebページ・サイトのデータを一括収集する

本記事では、複数のWebページ・URLのデータを一括でエクセル(Excel)にて取得し、テーブルとして読み込む方法をご紹介します。複数データを一括収集できるようになるため、非常に効率的に作業を行うことができるようになります。

コンテンツ

結論
前提：Webページ・サイトのデータをエクセルに取り込む方法
準備：Webページ・サイトのURL取得とテーブル番号の確認
Webページ・サイトのデータを一括収集するVBAの実行
おわりに

結論

以下のテーブルをA1セルから順に用意した上で、VBAを実行すると複数のWebページ・サイトのデータを一括で取得し、取り込むことができます。

用意するエクセルシート

Sub ImportTablesWithPowerQuery()
    ' Written by consulting-campus.site
    '
    '
    '
    Dim ws As Worksheet
    Dim int_LastRow As Long
    Dim i As Long
    Dim str_URL As String, str_QueryName As String, str_Formula As String
    Dim int_TabelNum As Integer

    ' エクセルの高速化
    Call fml_ExcelVbaStart

    ' アクティブワークシートを設定
    Set ws = ThisWorkbook.ActiveSheet

    ' A列の最後の行を取得
    int_LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 2行目から最後の行まで繰り返し処理を行う
    For i = 2 To int_LastRow
        str_URL = ws.Cells(i, 1).Value
        int_TabelNum = ws.Cells(i, 2).Value - 1
        str_QueryName = ws.Cells(i, 3).Value

        ' パワークエリで実行する処理を記述
        str_Formula = "let" & Chr(13) & _
                  "    Source = Web.Page(Web.Contents(""" & str_URL & """))," & Chr(13) & _
                  "    Data = Source{" & int_TabelNum & "}[Data]" & Chr(13) & _
                  "in" & Chr(13) & _
                  "    Data"

        ' クエリを作成/更新
        With ThisWorkbook.Queries.Add(Name:=str_QueryName, formula:=str_Formula)
            .Description = "Query created from row " & i
        End With

        ' クエリをワークシートにロード
        ThisWorkbook.Worksheets.Add
        ActiveSheet.Name = str_QueryName
        ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & str_QueryName & ";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable.CommandType = xlCmdSql
        ActiveSheet.ListObjects(1).QueryTable.CommandText = "SELECT * FROM [" & str_QueryName & "]"
        ActiveSheet.ListObjects(1).QueryTable.Refresh BackgroundQuery:=True
    Next i

    ' 高速化処理のリセット
    Call fml_ExcelVbaEnd

    MsgBox ("完了しました")
End Sub

Sub fml_ExcelVbaStart()
    Application.ScreenUpdating = False            '画面更新の停止
    Application.Cursor = xlWait                   'カーソルの停止
    Application.EnableEvents = False              'イベントの停止
    Application.DisplayAlerts = False             'アラートの停止
    Application.Calculation = xlCalculationManual '自動計算の停止
End Sub

Sub fml_ExcelVbaEnd()
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
End Sub

前提：Webページ・サイトのデータをエクセルに取り込む方法

取り込みたいWebページ・サイトが1つである場合、下記のページでWeb上のデータをエクセルに取り込む方法をご紹介しています。VBAを使うことに抵抗がある方や、取り出すデータ先が多くない場合は、こちらの方法もご検討ください。

今回の記事では、Webページ・サイトのデータをエクセルに取り込む動作をVBAにより自動化したバージョンをご紹介しています。

準備：Webページ・サイトのURL取得とテーブル番号の確認

概要

まずはじめに、前準備として下記3点を行います。それぞれの詳細について以降の各セクションで解説します。

Webページ・サイトのデータをエクセルに取り込む準備

ステップ1
URLの取得

WebページやサイトのURLをExcelのA列にリストアップします
ステップ2
テーブル番号の確認

取得したいテーブルがページ上で何番目に位置しているかを確認し、B列に番号を記入します
ステップ3
クエリ名及びシート名の指定

クエリ名及びシート名として登録したい名前をC列に入力します

具体的なステップ

URLの取得

まず、自身でデータを取得したいページにアクセスし、URLをA列に入力します。ここでは、アサヒ飲料の商品一覧ページを例として解説します。A列へのURLのリストアップは下記のステップで行います。

URLから法則性を特定:
例えば、今回のアサヒ飲料の商品一覧ページの場合、https://www.asahiinryo.co.jp/products/list/jan/0001.htmlというリンクのように0001から連番にページが用意されていることが推測できます。他にも、xxx.com/search?key={商品名}など、サイト・ページに応じて確認してください。

イメージ解説

エクセルで法則に従い複数のURLを作成:
上記のように作成したいページが確認できた場合、Excel上で関数を使用して任意の文字列をA列に入力してください。例えば、下記のような方法が想定されます。

イメージ解説

テーブル番号の確認

次に、テーブル番号の確認を行います。全データを自動で読み込むために、ナビゲーターウィンドウ上でテーブル番号を指定する必要があります。以下のステップで指定のテーブル番号を確認してください。

1つ目のデータを手動で読み込み、テーブル番号を確認:
[データ]→[Webから]→[ナビゲータ]ウィンドウ上にて、取得したいテーブルが上から何番目に表示されているか確認します。
詳細な解説はこちらのページをご確認ください。

イメージ解説

全行にテーブル番号を指定:
こちらのテーブル番号をエクセル内のB列に入力します。

イメージ解説

クエリ名の指定

最後に、クエリ名及びシート名として設定したい任意の名前を各行に入力します。取得したテーブルの内容がわかるように設定すると良いと思います。

イメージ解説

Webページ・サイトのデータを一括収集するVBAの実行

エクセル上での準備が完了したら、VBAを実行します。Alt+F11からビジュアルエディタを起動し、下記のVBAを実行してください。

Sub ImportTablesWithPowerQuery()
    ' Written by consulting-campus.site
    '
    '
    '
    Dim ws As Worksheet
    Dim int_LastRow As Long
    Dim i As Long
    Dim str_URL As String, str_QueryName As String, str_Formula As String
    Dim int_TabelNum As Integer

    ' エクセルの高速化
    Call fml_ExcelVbaStart

    ' アクティブワークシートを設定
    Set ws = ThisWorkbook.ActiveSheet

    ' A列の最後の行を取得
    int_LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 2行目から最後の行まで繰り返し処理を行う
    For i = 2 To int_LastRow
        str_URL = ws.Cells(i, 1).Value
        int_TabelNum = ws.Cells(i, 2).Value - 1
        str_QueryName = ws.Cells(i, 3).Value

        ' パワークエリで実行する処理を記述
        str_Formula = "let" & Chr(13) & _
                  "    Source = Web.Page(Web.Contents(""" & str_URL & """))," & Chr(13) & _
                  "    Data = Source{" & int_TabelNum & "}[Data]" & Chr(13) & _
                  "in" & Chr(13) & _
                  "    Data"

        ' クエリを作成/更新
        With ThisWorkbook.Queries.Add(Name:=str_QueryName, formula:=str_Formula)
            .Description = "Query created from row " & i
        End With

        ' クエリをワークシートにロード
        ThisWorkbook.Worksheets.Add
        ActiveSheet.Name = str_QueryName
        ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & str_QueryName & ";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable.CommandType = xlCmdSql
        ActiveSheet.ListObjects(1).QueryTable.CommandText = "SELECT * FROM [" & str_QueryName & "]"
        ActiveSheet.ListObjects(1).QueryTable.Refresh BackgroundQuery:=True
    Next i

    ' 高速化処理のリセット
    Call fml_ExcelVbaEnd

    MsgBox ("完了しました")
End Sub

Sub fml_ExcelVbaStart()
    Application.ScreenUpdating = False            '画面更新の停止
    Application.Cursor = xlWait                   'カーソルの停止
    Application.EnableEvents = False              'イベントの停止
    Application.DisplayAlerts = False             'アラートの停止
    Application.Calculation = xlCalculationManual '自動計算の停止
End Sub

Sub fml_ExcelVbaEnd()
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
End Sub

VBAの実行方法に関しては下記の記事で詳細に解説しています。こちらのコードの実行方法がわからない方はご確認ください。

なお、今回使用するVBAコードの動作を大まかな説明は下記のとおりです。こちらのコードはVBAの知識がなくても使用できるようになっています。こちらのVBAを実行すると、各シートにWeb・サイト上からデータを取得するクエリが一括でロードされ、一定時間の経過後に結果がテーブルとして取得・表示されます。

ウェブページからのデータのインポート:
- ワークシートの2行目から最終行までをループし、各行の情報を使用してWebページ・サイト上からデータを取得します
- 各行ごとに、URL(str_URL)、テーブル番号(int_TabelNum)、およびクエリ名(str_QueryName)を取得します
- str_FormulaにPower Queryのクエリコマンドを設定します。このコマンドは、指定されたURLからデータを取得し、指定されたテーブル番号のデータを抽出します
クエリの追加と更新:
- 取得したデータに基づいて、新しいクエリを作成または既存のクエリを更新します
- C列で指定したクエリ名が設定されます
データのワークシートへのロード:
- 新しいワークシートを追加し、名前をクエリ名と同様に指定したシート名が設定されます
- Power Queryクエリを使用して、データを新しいワークシートのテーブルにロードします