VBAサンプル集
WEBデータの取得方法

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
公開日:2013年5月以前 最終更新日:2019-10-25

WEBデータの取得方法


WEBページのデータを取得して、エクセルのデータとして取り込みたいとの要望が多いようです。


マクロVBAでWEBページのデータを取得する方法はいろいろあります。
QueryTables
InternetExplorer
MSHTML
MSXML2

順番に、以下で説明します。

サンプルページ

まず、サンプルページとして、ヤフーファイナンスの、以下のページを使います。

vba マクロ webページ取得

http://info.finance.yahoo.co.jp/ranking/?kd=1&mk=1&tm=d&vl=a
このページの一覧表の部分をエクセルに取り込みます。
このページは、ランキングですので、複数ページあります。
上のURLは、最初のページです。
では2ページ目はと言うと、
http://info.finance.yahoo.co.jp/ranking/?kd=1&tm=d&vl=a&mk=1&p=2
このようなURLになります。
この場合、最期のp=2がページ数になっています。
これを変更すれば、各ページが取得できます。
p=1なら、最初のページも取得可能です。
従って、以降のマクロでは
http://info.finance.yahoo.co.jp/ranking/?kd=1&tm=d&vl=a&mk=1&p=1
として取得しています。
この数値を順次変更して取得すれば、複数ページの取得も可能です。

ただし、このページは近年アクセス方法に制限が書けられています。
そこで、制限のない当サイトの祝日一覧のページもサンプルとして使います。

QueryTables

まずは、
エクセル本来の機能で、Webクエリです。

Sub sample1()
  With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://info.finance.yahoo.co.jp/ranking/?kd=1&tm=d&vl=a&mk=1&p=1", _
    Destination:=Range("$A$1"))
    .Name = "?kd=1&tm=d&vl=a&mk=1&p=1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
  End With
End Sub

太字のxlAllTablesの指定で、tableのみ取得しています。
これで済む場合は、これが最も簡単です。
マクロの記録を使えば、そのまま使えます。
上記も、マクロの記録のままです。

InternetExplorer

次は、
InternetExplorerを、VBAから操作します。

Sub sample2()
  Dim i As Long
  Dim j As Long
  
  Dim objITEM As Object
  Dim objIE As New InternetExplorer
  objIE.Visible = False
  
  objIE.Navigate "http://info.finance.yahoo.co.jp/ranking/?kd=1&tm=d&vl=a&mk=1&p=1"
  Call untilReady(objIE)
  
  Range("A1").CurrentRegion.Offset(1, 0).ClearContents
  i = 2
  j = 1
  For Each objITEM In objIE.Document.getElementsByTagName("td")
    Cells(i, j) = objITEM.innerText
    j = j + 1
    If j > 10 Then
      j = 1
      i = i + 1
    End If
  Next
  objIE.Quit
  Set objITEM = Nothing
  Set objIE = Nothing
End Sub

Sub untilReady(objIE As Object, Optional ByVal WaitTime As Integer = 10)
  Dim starttime As Date
  starttime = Now()
  Do While objIE.Busy = True Or objIE.ReadyState <> READYSTATE_COMPLETE
    DoEvents
    If Now() > DateAdd("S", WaitTime, starttime) Then
      Exit Do
    End If
  Loop
  DoEvents
End Sub
※「Microsoft Internet Control」を参照設定して下さい。

問題は、HTMLを取得後の解析になります。
For Each objITEM In objIE.Document.getElementsByTagName("td")
Tableのtrの中のtdで、必要なデータのみ取得しています。
これについては、VBAの知識ではなく、HTMLの知識が必要になります。
Table、tr、td等が分からない場合は、HTMLの勉強をして頂く必要があります。
ここでは、HTMLの解説は省略します。

※注意
untilReady内のループでは、本来なら、Sleepさせた方が良いのですが、
APIの使用になるので、ここでは省略しています。
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
を先頭に記述し、
ループ内で、適宜、
Sleep 100
程度を入れておくと良いでしょう。

MSHTML

さらに、
MSHTML.HTMLDocumentを使った方法です。

Sub sample3()
  Dim i As Long
  Dim j As Long
  Dim objXML As New MSHTML.HTMLDocument
  Dim htmlDoc As New MSHTML.HTMLDocument
  Dim objITEM As Object

  Set htmlDoc = objXML.createDocumentFromUrl("https://excel-ubara.com/EXCEL/EXCEL910.html", vbNullString)
  Call untilReady(htmlDoc)

  Range("A1").CurrentRegion.Offset(1, 0).ClearContents
  i = 2
  j = 1
  For Each objITEM In htmlDoc.getElementsByTagName("td")
    Cells(i, j) = objITEM.innerText
    j = j + 1
    If j > 3 Then
      j = 1
      i = i + 1
    End If
  Next

  Set objITEM = Nothing
  Set htmlDoc = Nothing
  Set objXML = Nothing
End Sub
Sub untilReady(htmlDoc As MSHTML.HTMLDocument, Optional ByVal WaitTime As Integer = 10)
  Dim starttime As Date
  starttime = Now()
  Do Until htmlDoc.ReadyState = "complete"
    DoEvents
    If Now() > DateAdd("S", WaitTime, starttime) Then
      Exit Do
    End If
  Loop
  DoEvents
End Sub
※「Microsoft HTML Object Library」を参照設定して下さい。

先のヤフーファイナンスでは、この方法ではアクセスできなくなっています。
以前は、この方法でも取得できたのですが、近年はいろいろ制限のあるページが増えてきたようです。
そこで、当サイトの祝日一覧のページを取得するようにしています。

InternetExplorerの部分がMSHTML.HTMLDocumentになった程度で、
後は、ほとんど同じです。

MSXML2

さらに、
MSXML2.XMLHTTPを使った場合です。

Sub sample4()
  Dim i As Long
  Dim j As Long
  Dim objXML As New MSXML2.XMLHTTP
  Dim htmlDoc As Object
  Set htmlDoc = New MSHTML.HTMLDocument
  Dim objITEM As Object

  With objXML
    .Open "GET", "https://excel-ubara.com/EXCEL/EXCEL910.html", False
    .send (Null)
    htmlDoc.write .responseText
  End With
 
  Range("A1").CurrentRegion.Offset(1, 0).ClearContents
  i = 2
  j = 1
  For Each objITEM In htmlDoc.getElementsByTagName("td")
    Cells(i, j) = objITEM.innerText
    j = j + 1
    If j > 3 Then
      j = 1
      i = i + 1
    End If
  Next

  Set objITEM = Nothing
  Set htmlDoc = Nothing
  Set objXML = Nothing
End Sub
※「Microsoft XML, v3.0」(v3.0以上を指定して下さい)を参照設定して下さい。

サンプルとして書いてみましたが、HTMLの解析ができないので、
結局、MSHTML.HTMLDocumentを使う事になってしまいました。
また、
Dim htmlDoc As New MSHTML.HTMLDocument
としたのでは、
htmlDoc.writeでエラーになってしまいますので、ちょっと面倒です。
結果的には、MSHTML.HTMLDocumentの方で良いのではないかという気がします。

WEBデータの取得方法の最後に

番外編としては、Seleniumを使う方法もあります。
VBAのスクレイピングを簡単楽にしてくれるSelenium
・SeleniumBasicのインストール ・VBEでの参照設定 ・WEBサイトを表示してみましょう ・Seleniumの基本的な使い方(株価情報を取得してみる) ・色々なパターンでのseleniumの使い方 ・色々組み合わせて目的の画面にたどり着きます ・elementをコレクションで取得する ・新規ページが開かれる場合 ・上手くいかない特殊な場合の対処方法 ・Seleniumの実践例例 ・最後に
VBA+SeleniumBasicで検索順位チェッカー作成
・シート構成 ・検索順位チェッカーのVBA全コード ・最後に
VBA+SeleniumBasicで検索順位チェッカー(改)
・シート構成 ・検索順位チェッカーのVBA全コード ・最後に

いずれの方法にしても、
最初のWebクエリ以外では、HTMLの知識が必要になります。
ここが書けない場合は、Webクエリでシートに貼り付けた後で、
シート内のデータを解析して、必要なデータだけに編集する等が良いでしょう。



同じテーマ「マクロVBAサンプル集」の記事

ストップウォッチ(1/100秒)(Timer)

・ストップウォッチVBAコード ・ストップウォッチVBA解説 ・ストップウォッチ機能強化版
ストップウォッチ改(1/100秒)(Timer)
・公開済みの初期のストップウォッチ ・追加する機能 ・シートレイアウト ・VBAコード ・ダウンロード
重複の無いユニークなデータ作成
簡単な例で シート「元データ」A列に、1行目に見出し、2行目以降にデータが入っている シート「ユニーク」このA列に、シート「元データ」のA列をユニーク(一意)にして取り出します。まずは、エクセルらしく、ワークシート関数とフィルターを使って 非常にエクセルらしい、素直な処理です。
WEBデータの取得方法
右クリックメニューの変更(CommandBars)
・シートモジュールに以下を追加 ・標準モジュールに以下を追加 ・コマンドバー.Controls.Add() ・コントロールのメンバーについて ・CommandBarsの種類
エクセルのアイコン取得(FaceID)
エクセル内のアイコンを取得します。「右クリックメニューの変更」のFaceIdとして使用します。2003までなら、ツールバーが追加されます。2007以降では、リボンのアドインの中に追加されます。ConststartNoAsInteger=1'開始番号を指定 ConststopNoAsInteger=50'終了番号を指…
素数を求めるマクロ
素数とは、1と自分自身以外に正の約数を持たない、1でない自然数のことです、この素数を求めて、シートに出力するマクロになります。特に何かに使えると言う事もないので、PCの計算能力って、どの程度なのかを実感してみるくらいの事でしょうか。注意 このマクロはオーバーフローするまで停止しません。
入力規則のリスト入力の妥当性判定
・入力規則に反するデータかの判定Function ・入力規則のリスト入力の妥当性を判定Function ・最後に
配色を使用したカラー設定を固定カラーに変更
Excel2007以降なら配色を選択して作成した場合、作成したシートを他のブックに移すと色が変わってしまいます、そこで、配色ではなくRGB値で色を再設定することで、元々の色をそのままにして、他のブックに移すことが出来るようにします。以下は、この目的で色を再設定するマクロVBAになります。
指定セルに名前定義されているか判定する
・指定セル範囲が何らかの名前定義に含まれているか ・指定セル範囲と同一範囲の名前定義があるか ・すでに設定されている名前定義を削除してから再設定 ・マクロVBAでの名前定義について
Excel2003(xls)を2007以降(xlsx,xlsm)に変換する(HasVBProject)
Excel2003形式(xls)のファイルを、一括でExcel2007以降形式(xlsx,xlsm)に変換するマクロVBAサンプルコードです。サンプルコードでは、サブフォルダ「Excelファイル」にあるxlsを、マクロ無しならxlsx、マクロ有りならxlsmにして保存しています。


新着記事NEW ・・・新着記事一覧を見る

テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ