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

このページの一覧表の部分をエクセルに取り込みます。
このページは、ランキングですので、複数ページあります。
上の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
For Each objITEM In objIE.Document.getElementsByTagName("td")
Tableのtrの中のtdで、必要なデータのみ取得しています。
これについては、VBAの知識ではなく、HTMLの知識が必要になります。
Table、tr、td等が分からない場合は、HTMLの勉強をして頂く必要があります。
ここでは、HTMLの解説は省略します。
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
以前は、この方法でも取得できたのですが、近年はいろいろ制限のあるページが増えてきたようです。
そこで、当サイトの祝日一覧のページを取得するようにしています。
後は、ほとんど同じです。
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
結局、MSHTML.HTMLDocumentを使う事になってしまいました。
また、
Dim htmlDoc As New MSHTML.HTMLDocument
としたのでは、
htmlDoc.writeでエラーになってしまいますので、ちょっと面倒です。
結果的には、MSHTML.HTMLDocumentの方で良いのではないかという気がします。
WEBデータの取得方法の最後に
VBAのスクレイピングを簡単楽にしてくれるSelenium
いずれの方法にしても、
最初のWebクエリ以外では、HTMLの知識が必要になります。
ここが書けない場合は、Webクエリでシートに貼り付けた後で、
シート内のデータを解析して、必要なデータだけに編集する等が良いでしょう。
同じテーマ「マクロVBAサンプル集」の記事
ストップウォッチ(1/100秒)(Timer)
ストップウォッチ改(1/100秒)(Timer)
重複の無いユニークなデータ作成
WEBデータの取得方法
右クリックメニューの変更(CommandBars)
エクセルのアイコン取得(FaceID)
素数を求めるマクロ
入力規則のリスト入力の妥当性判定
配色を使用したカラー設定を固定カラーに変更
指定セルに名前定義されているか判定する
Excel2003(xls)を2007以降(xlsx,xlsm)に変換する(HasVBProject)
新着記事NEW ・・・新着記事一覧を見る
VBA100本ノック 79本目:Wordにセル範囲を図として挿入|VBA練習問題(2月1日)
VBA100本ノック 78本目:グラフのデータ範囲拡張|VBA練習問題(1月30日)
VBA100本ノック 77本目:シート挿入イベント|VBA練習問題(1月29日)
VBA100本ノック 76本目:ボタンの表示名の位置へ移動|VBA練習問題(1月27日)
VBA100本ノック 75本目:ログインフォームの作成|VBA練習問題(1月26日)
VBA100本ノック 74本目:1シート複数表をDB形式に変換|VBA練習問題(1月25日)
VBA100本ノック 73本目:新規ブックにボタン作成しマクロ登録|VBA練習問題(1月23日)
VBA100本ノック 72本目:ITをDXに変換(文字列操作)|VBA練習問題(1月22日)
VBA100本ノック 71本目:パワーポイントへグラフを貼り付け|VBA練習問題(1月20日)
VBA100本ノック 70本目:ステータスバーに1秒ごとに時刻が表示|VBA練習問題(1月19日)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- WEBデータの取得方法
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。