Office
 Computer >> コンピューター >  >> ソフトウェア >> Office

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

World Wide Webには有用なデータが膨大に存在することは周知のとおりです。しかし、何らかの分析を行う前には、そのデータをMicrosoft Excelに取り込む必要があります。本記事では、このタスクを完了させるために使える2つの方法を詳しく解説します。

方法1：Webクエリ機能で外部データを手動取得する

ここでは、興行収入ランキング上位の映画データをWebページからダウンロードするケースを例に、手順をわかりやすく紹介します。

まずMicrosoft Excelを開き、「データ」タブをクリックします。次に「外部データの取込」グループ内の「Web」をクリックしてください。「新しいWebクエリ」ダイアログボックスが表示されたら、Webアドレス(https://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office)を「アドレス」欄にコピーして「移動」ボタンを押します。すると、図1.1のようにExcelがWebページのダウンロードを開始します。下図のような「スクリプトエラー」警告ボックスが表示された場合は、「いいえ」をクリックすればOKです。警告は消え、インポート処理への影響もありません。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図1.1

「新しいWebクエリ」ダイアログボックスの右上にある黄色いボックス内には矢印アイコンがあります。これをクリックすることで、テーブルの前に類似アイコンを表示するかどうかを切り替えられます。たとえば図1.2の左パネルではテーブルの横に矢印アイコンがありませんが、矢印ボタンをクリックしてアイコン表示をオンにすると、右パネルのようにアイコンが現れます。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図1.2(画像をクリックすると拡大表示されます)

取り込みたいテーブルの隣にある矢印アイコンをクリックします。アイコンとテーブルの表示が変わり、図1.3の左パネルのような状態になります。「インポート」をクリックすると「データのインポート」ダイアログボックスが表示されるので、データを配置する範囲(本例ではA列からH列まで)を指定して「OK」をクリックしましょう。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図1.3(画像をクリックすると拡大表示されます)

「OK」をクリックすると、データがExcelにインポートされます。テーブル内の任意のセルを右クリックして「更新」を選択すれば、ExcelがWebページにアクセスし、最新のデータを再取得してくれます。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図1.4

さらに、クエリデータの更新方法も自由に設定できます。テーブル内の任意のセルを右クリックして「データ範囲プロパティ」を選択し、表示された「外部データ範囲のプロパティ」ダイアログボックスで「更新コントロール」の設定を変更してください。たとえば「60分ごとに更新する」や「ファイルを開いたときに更新する」といった指定が可能です。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

方法2：VBAプログラミングでデータをスクレイピングする

VBAプログラミングを使えば、Webページからデータを自動的に抽出(スクレイピング)できます。1つ目の方法に比べて応用範囲が広い反面、難易度も高くなります。また、VBAでスクレイピングを学ぶ前に、HTMLとは何かを理解しておく必要があります。HTMLについてまったく知らない、あるいは知識が少ないという方は、基礎的なHTMLの学習サイトで予習しておくことをおすすめします。本記事では、代表的な2つのサンプルを紹介します。

例1：単一のWebページからデータを抽出する

あるWebページから、会社名・メールアドレス・担当者名を抽出したいとします。このページを開くと、下部に連絡先ブロックがあるのがわかります。図2.1には、連絡先ブロックとそれに対応するソースコードが並んでいます。赤枠内の情報が取得対象で、緑の下線が引かれた部分がまさに抽出すべき要素です。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図2.1

以下のコードを使うと、目的の情報を抽出して最初のワークシートに書き出せます。

ソースコード
Sub Retrieve_Click()
 
'Create InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
 
'Let's not see the browser window
IE.Visible = False
 
'Open the web page
IE.Navigate "https://www.austrade.gov.au/SupplierDetails.aspx?ORGID=ORG8160044431&folderid=1736"
 
'Wait while IE is loading
Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Retrieve company name, email address & contact information
Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
MsgBox htext
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(1, 1) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(1).Cells(2, 1) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(3, 1) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(1).Cells(4, 1) = IE.LocationURL
 
ThisWorkbook.Save
 
Set IE = Nothing
 
Set contactobj = Nothing
 
End Sub

「IE.document.getElementsByClassName("contact-details block dark")」により、class名が「contact-details block dark」であるすべての要素を取得できます。HTML要素に対して使えるプロパティやメソッドの一覧は各種リファレンスサイトで確認できるので、自分の課題に合ったものを選びましょう。

innerHTMLプロパティを使うと、HTML要素の内容を設定または返却できます。本例では、class名「contact-details block dark」を持つ要素の内容を変数htextに格納しています。「Msgbox htext」を実行すると、その内容(図2.2)がポップアップ表示されます。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図2.2

ご覧のとおり、テキストはきれいに構造化されています。だからこそSPLIT関数で必要な情報を取り出せるのです。たとえば「<p>Company Name:」を区切り文字にすると、「Split(htext, "<p>Company Name: ")(1)」は「<p>Company Name:」より後ろの全テキストを返します。さらに、その戻り値に対して「<br」を区切り文字にすれば、最初の「<br」より前のテキスト、つまり会社名だけを取得できます。要するに、SPLIT関数はほぼあらゆるものを抽出できる柔軟なツールなのです。ほかにもLEN、INSTR、LEFT、RIGHT、MID、REPLACEなどの便利な関数がありますが、詳細は割愛します。

図2.2で「OK」をクリックすると、要求したデータがWebからExcelワークシートに取り込まれます。たとえばセルA1には会社名、セルA4には会社のWebページアドレスが入ります。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図2.3

ブックを保存する前に以下のコードを追加すると、セルA4にハイパーリンクを設定できます。

ソースコード
'Add hyperlink
ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)

セルA4をクリックすれば、該当のWebページに再度アクセスできます。多数の企業のデータを収集する場合、後からレビューしながら手動で情報を追加・更新する際に、ハイパーリンクから元ページへすぐ戻れるのは非常に便利です。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図2.4

関連記事

  • Excel VBAで他ブックを開いてデータをコピーする方法
  • [解決済み!] WorkbooksオブジェクトのOpenメソッドが失敗する(4つの対策)
  • Excel VBAでセル値を配列に格納する(実用例4選)
  • VBAでブックを開いてマクロを実行する方法(4つの例)

例2：Webページと対話しながら大量データを取得する

上記の例は、静的な単一ページからのデータ取得でした。しかし実際には、大量のデータを取得するためにWebページと対話(操作)することが求められるケースが多々あります。図3.1を見てください。これは前述の例のページに至るまでの流れを示したものです。業種(Industry)がたくさんあり、それぞれの業種の下に多数の企業が存在します。たとえばAgribusiness(農業ビジネス)業種には651社あります。では、全業種・全企業の連絡先情報を抽出するにはどうすればよいのでしょうか。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図3.1(画像をクリックすると拡大表示されます)

ポイントは、「人間が手動で行う操作を、いかにVBAに再現させるか」です。S.W.I.S Advantageを例に説明します。通常なら、まずExcel側でAgribusiness(図3.1上部)をクリックさせ、IEを2つ目のWebページへ遷移させます。続いて2つ目のページ(図3.1下部)でS.W.I.S Advantageをクリックさせれば、図2.1のページへ移動し、同社の連絡先情報を取得できます。

以下のコードをVisual Basic Editorに入力して実行すると、IEが起動し、最初のページが表示された後に2つ目のページへ移動します。ここでは、ドロップダウンリスト要素の取得方法、オプションの選択方法、そしてオプション選択後にイベントを発火させる方法を学べます。「m = IE.document.getElementsByTagName("option").Length – 1」は選択肢の総数を返し、次のループ処理などに利用できます。

ソースコード
Sub retrieve()
 
'Create InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window
IE.Visible = True
 
'Open the web page
IE.Navigate "https://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 1 - Select dropdown list and trigger event after you select one option
Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = 1
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading
Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
End Sub

このコード部分により、Excelが最初の企業名をクリックした後に図2.1のページへ遷移します。全企業名はclass名「Name」を持つ要素に含まれています。searchobjはコレクションであり、searchobj(i)は(i+1)番目のオブジェクトを返します。たとえば「searchobj(1).Click」を実行すると、RIDLEY CORPORATION(メルボルン)のページへアクセスできます。

ソースコード
'Part 2 - Select company Name
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(0).Click
 
'Wait while IE is loading
Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop

最後に、IEの起動からページ閲覧、データ抽出までの一連の流れを示す完全なコードを紹介します。抽出結果は図2.4と同じになります。

ソースコード
Sub Retrieve()
 
'Create InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window
IE.Visible = True
 
'Open the web page
IE.Navigate "https://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 1 - Select dropdown list and trigger event after you select one option
Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = 1
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading
Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
'Part 2 - Select company Name
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(0).Click
 
'Wait while IE is loading
Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information
Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(1, 1) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(1).Cells(2, 1) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(1).Cells(3, 1) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(1).Cells(4, 1) = IE.LocationURL
 
'Add hyperlink
ThisWorkbook.Worksheets(1).Hyperlinks.Add ThisWorkbook.Worksheets(1).Cells(4, 1), ThisWorkbook.Worksheets(1).Cells(4, 1)
 
End Sub

実際にやりたいことは「全業種・全企業の連絡先情報を抽出する」ことなので、Forループ文を使ってこのタスクを完了させる必要があります。以下が完全版のコードです。このコードは、記事末尾からダウンロードできるRetrieve contact information for all companies.xlsmでも確認できます。

ソースコード
Sub Retrieve()
 
For idex = 2 To 18
 
'Create InternetExplorer
Set IE = CreateObject("InternetExplorer.Application")
 
'Let's see the browser window
IE.Visible = False
 
'Open the web page
IE.Navigate "https://www.austrade.gov.au/international/buy#"
 
'Wait while IE is loading
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
idexn = idex - 1
 
'Part 1 - Select dropdown
Set selectobj = IE.document.getElementsByTagName("select")
 
m = IE.document.getElementsByTagName("option").Length - 1
 
selectobj(0).selectedIndex = idexn
 
selectobj(0).FireEvent ("onchange")
 
'Wait while IE is loading
Do While IE.readyState <> 4 Or IE.Busy = True
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
Application.Wait (Now + TimeValue("00:00:10"))
 
wurl = IE.LocationURL
 
tot = IE.document.getElementsByClassName("SearchTotal")(0).innerHTML
 
pg = Int(tot / 25) + 1
 
Max = (tot Mod 25) - 1
 
'Part 2 - Select Class = "Name"
a = 2
 
For j = 1 To pg
 
If j = 1 Then
 
IE.Navigate (wurl)
 
Else
 
IE.Navigate (wurl & "&pg=" & j)
 
End If
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
If j <> pg Then
 
For i = 1 To 24
 
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(i).Click
 
'Wait while IE is loading
Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information
Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
ThisWorkbook.Worksheets(idex).Cells(a, 1) = j
 
ThisWorkbook.Worksheets(idex).Cells(a, 2) = a - 1
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 3) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 4) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 5) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(idex).Cells(a, 6) = IE.LocationURL
 
IE.GoBack
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
a = a + 1
 
Next i
 
Else
 
For i = 0 To Max
 
Set searchobj = IE.document.getElementsByClassName("Name")
 
searchobj(i).Click
 
'Wait while IE is loading
Do While IE.readyState <> 4 Or IE.Busy = True
 
DoEvents
 
Loop
 
'Part 3 - Retrieve company name, email address & contact information
Set contactobj = IE.document.getElementsByClassName("contact-details block dark")
 
htext = contactobj(0).innerHTML
 
ThisWorkbook.Worksheets(idex).Cells(a, 1) = j
 
ThisWorkbook.Worksheets(idex).Cells(a, 2) = a - 1
 
If InStr(htext, "<p>Company Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 3) = Split(Split(htext, "<p>Company Name: ")(1), "<br")(0)
 
End If
 
If InStr(htext, "mailto:") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 4) = Split(Split(htext, "mailto:")(1), Chr(34) & ">")(0)
 
End If
 
If InStr(htext, "<p>Name: ") Then
 
ThisWorkbook.Worksheets(idex).Cells(a, 5) = Split(Split(htext, "<p>Name: ")(1), "<br")(0)
 
End If
 
ThisWorkbook.Worksheets(idex).Cells(a, 6) = IE.LocationURL
 
ThisWorkbook.Worksheets(idex).Hyperlinks.Add ThisWorkbook.Worksheets(idex).Cells(a, 6), ThisWorkbook.Worksheets(idex).Cells(a, 6)
 
IE.GoBack
 
Do While IE.Busy
 
Application.Wait DateAdd("s", 1, Now)
 
Loop
 
a = a + 1
 
Next i
 
End If
 
ThisWorkbook.Save
 
Next j
 
Set IE = Nothing
 
Set contactobj = Nothing
 
Next idex
 
End Sub

補足が必要な点は図3.2に関わる部分だけです。1ページに表示できる企業数は最大25社です。総企業数が25社を超えると複数ページに分かれます。図3.2のとおり、2ページ目以降のURLには規則性があり、「1ページ目のアドレス+『&pg=』+実際のページ番号」という連結で求められます。また、最終ページ以外では1ページあたりのオブジェクト数は25です。「IE.document.getElementsByClassName("SearchTotal")(0).innerHTML」は業種内の総企業数を返します。本例では651です。「Int(tot / 25) + 1」で総ページ数が、「Max = (tot Mod 25) – 1」で最終ページの最大企業数(インデックス)が得られます。あとはこの考え方をコードにどう適用するか、ぜひご自身で考えてみてください。自分で組み立てるほうがコードの理解が深まります。疑問があればコメントでお気軽にどうぞ。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図3.2

完成したExcelファイルの一部を紹介します。同一業種内の全企業の連絡先情報が、1枚のワークシートにまとめられています。

WebサイトからExcelへデータを取り込む2つの方法｜手動インポートとVBAスクレイピングを徹底解説

図3.3(画像をクリックすると拡大表示されます)

作業ファイルのダウンロード

作業ファイルは以下のリンクからダウンロードできます。

Pull-Data-from-Web-to-Excel.rar

関連記事

  • WebサイトからExcelへデータを自動抽出するには?
  • WordからExcelへデータをインポートする方法(文章・段落・表・コメント対応)
  • 初心者〜上級者向けのおすすめExcel VBA書籍6選
  • Excel VBAプログラミング&マクロの無料チュートリアル(ステップバイステップ)
  • Excel VBAコーディングのコツ
  • VBAでできること一覧
  • VBAマクロ入門
  1. Excelでアンケートの質的データを分析する方法｜初心者向けステップ別ガイド

    この記事では、Excelを使ってアンケートの質的データを分析する方法を解説します。アンケートには複数の質問が含まれており、特に自由記述式の回答など「質的データ」の扱いに悩むExcelユーザーは少なくありません。質的データを正しく分析するには、いくつかの具体的なステップを踏む必要があります。本記事では、その手順を分かりやすく順番にご紹介します。それでは早速始めましょう。 練習用ファイルのダウンロード 練習用のワークブックはこちらからダウンロードできますので、ぜひ活用してください。 Excelでアンケートの質的データを分析する手順 ここでは、企業が実施したアンケートの中の「自由回答形式の質問」を例

  2. Excelでデータモデルからデータを取得する2つの簡単な方法

    Excelのデータモデルからデータを取り出す便利なテクニックをお探しの方は、ぜひこの記事をご覧ください。データモデルからデータを取得する方法は実にさまざまありますが、本記事では代表的な2つの方法を詳しく解説します。手順を一つずつ丁寧に追って説明しますので、最後まで読んでマスターしましょう。 データモデルとは? データモデルはデータ分析において欠かせない機能です。データモデルを使うと、テーブルなどのデータをExcelのメモリ上に読み込むことができます。さらに、共通の列(キー列)を指定することで、複数のテーブル同士を関連付けることも可能です。データモデルにおける「モデル」という言葉は、各テーブル間