VBAサンプル集<br />WEBデータの取得方法
VBAサンプル集<br />WEBデータの取得方法

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

WEBページのデータを取得して、エクセルのデータとして取り込みたいとの要望が多いようです。マクロVBAでWEBページのデータを取得する方法はいろいろあります。QueryTables InternetExplorer MSHTML MSXML2 順番に、以下で説明します。

http://info.finance.yahoo.co.jp/ranking/?kd=1&mk=1&tm=d&vl=a このページの一覧表の部分をエクセルに取り込みます。 このページは、ランキングですので、複数ページあります。 上のURLは、最初のページです。 では２ページ目はと言うと、 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から操作します。

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

※「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を使った方法です。

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

※「Microsoft HTML Object Library」を参照設定して下さい。

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

InternetExplorerの部分がMSHTML.HTMLDocumentになった程度で、 後は、ほとんど同じです。 MSXML2 さらに、 MSXML2.XMLHTTPを使った場合です。

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

※「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サンプル集」の記事 新着記事 NEW ・・・新着記事一覧を見る アクセスランキング ・・・ ランキング一覧を見る このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。 掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。 本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。 This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.