Excel VBAでIEを操作 テーブルをエクセルのシートにコピー
2018/11/16
Excelで目的のURLを自動的に開き、そのページにあるハイパーリンクから次のページへと遷移。
検索窓に文字を入れ、検索先へと遷移する方法は前回例示しました。
今回はテーブルデータをエクセルに貼り付けることを例示したいと思います。
そこで今回のお題は
・「Yahoo!ファイナンス」で「任天堂」の株価のサイトを表示
・「任天堂」株価ページの時系列データのテーブルをエクセルのセルに貼り付ける
を実践。
上記お題は、以下の操作をすることにより可能となります。
・エクセルを起動
・（バージョンによっては）マクロを有効にする
・マクロの編集画面で、以下のコードをコピー＆ペースト
・マクロを実行
コピペするコード
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
'変数を必ず定義すると宣言 Option Explicit '変数を宣言 Dim objIE As InternetExplorer Dim ieDoc As HTMLDocument Dim obj As Object Dim obj2 As Object Dim obj3 As Object Dim j As Integer Dim i As Integer Sub IE_open() j = 0 '変数を初期化 'Internet Exploreを立ち上げる Set objIE = CreateObject("InternetExplorer.Application") objIE.Visible = True 'URLを指定する objIE.Navigate2 "https://stocks.finance.yahoo.co.jp/stocks/history/?code=7974" '該当のWebサイトが表示されるのを待つ While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE DoEvents Wend Set ieDoc = objIE.document 'tableタグを一つずつを変数objにセット For Each obj In ieDoc.getElementsByTagName("table") 'trタグを一つずつを変数objにセット For Each obj2 In obj.getElementsByTagName("tr") '行カウンターを１アップ j = j + 1 i = 0 For Each obj3 In obj2.getElementsByTagName("td") i = i + 1 'タグのテキスト内容をセルにセット Worksheets("Sheet1").Cells(j, i).Value = obj3.innerText Next Next Next End Sub |
マクロを実行したら、実行したExcelの"Sheet1"に「任天堂」の株価がコピペされていませんか？
（Yahoo!側でソース修正されたらうまく動かないかもしれません。）
ではソースの解説です。
Yahoo!ファイナンスを開くところまで（上記の青字箇所）は解説済みのために、それ以降を説明します。
（念のため、上記ソースにはおさらいの意味を込めて簡単なコメントを掲載しています。）
|
1 2 3 4 5 |
For Each Obj In ObjIE.document.all '表示されているサイトの全タグ一つずつを変数objにセット '各アンカータグ単位に以下の処理を実施 ・・・ Next |
前回までは、検索するタグを指定していました。
実際の指定方法は、
getElementsByTagName("タグ名")
とするとしていましたが、今回は全タグを対象とします。
というのも、複数のタグを対象にし、タグによって処理を振り分けたいからです。
全タグを指定する場合は、単純に
all
と指定するだけです。
続いてのコードです。
|
1 2 3 4 5 6 |
Select Case Obj.tagName 'タグ名により処理を分岐 Case "TR", "TD", "TH" 'TR,TD,THタグの場合、以下の処理を実施 ・・・ End Select |
Tableタグの構造の中でも、取得したいのはデータの項目用タグであるTDとヘッダーの項目用タグであるTHタグ。
そして、行の単位であるTRタグを抽出します。
（抽出後の利用方法は後述）
IF文で記載してもよかったのですが、or文が嫌いなので、Select命令を使っています。
タグ名はtagNameに収録されています。
ここで注意です。
「tagNameは大文字」であることに注意ください。
実際のタグが大文字であっても、小文字であっても、tagNameを小文字で記述した場合は、該当タグが処理されません。
続いてのコードです。
|
1 2 3 4 5 |
If Obj.offsetparent.className = "boardFin yjSt marB6" Then '親タグのCLASS名が「boardFin yjSt marB6」の場合 ・・・ End If |
該当のサイトには複数のtableタグがあります。
そのうち、株価が時系列に掲載されるtableのみを対象に今回は処理をします。
このtableタグをどうにか特定できないかと、目印をさがすと「Class」が指定されています。
以下は実際のYahoo!ファイナンスのコードです。
始値>
高値
|
1 2 3 4 5 6 7 |
<table class="boardFin yjSt marB6" border="0" width="100%" cellspacing="0" cellpadding="0"> <tbody> <tr> <th width="20%">日付</th> </tr> </tbody> </table> |
同一Classは同一ファイル何に複数設定が可能ですが、今回はたまたまclass="boardFin yjSt marB6"が幸いにもひとつだけ定義されていました。
そこで、これを目印に処理をします。
ただし、処理対象にしているのは、tableタグの子や孫、子孫要素であるTR,TH,TDの各タグです。
それぞれのタグには、IDやClassが設定されていません。
しかしながら、Excelでは当該タグの親要素（直上要素ではありません）のIDやClassを参照することが可能できるのです。
その方法が
offsetparent
です。
したがって、今回のようにtableタグのClassを参照したい場合は、上記ソースにあるとおり
offsetparent.className
と記述すればいいこととなります。
なお、offsetparentが設定されるのはTableなど親子関係のある一部のタグだけですので、ご注意ください。
そのため、
続いてのコードです。
|
1 2 3 4 5 6 7 8 9 10 |
Select Case Obj.tagName 'タグ名により処理を分岐 Case "TR" 'TRタグの場合 j = j + 1 '行カウンターを１アップ i = 0 '列カウンターをクリアー Case "TD", "TH" 'TD,THタグの場合、以下を処理 i = i + 1 '列カウンターを１アップ Worksheets("Sheet1").Cells(j, i).Value = Obj.innertext 'タグのテキスト内容をセルにセット End Select |
これは単純に行を表すTRタグの場合は、行をカウントアップさせ
TDもしくはTHタグの場合、列カウンターをカウントアップさせながら、タグ内のテキストをセルに貼り付けています。
なお、上記コードではセルが結合されている場合などに対応できませんので、次回はセルが結合されていた場合にも対応可能な方法を紹介します。
今回のポイントの整理です。
・全タグを個別に判定したい場合、 For Each Obj In ObjIE.document.all ～ Nextを使用
・タグ名を取得するにはtagNameを使用（大文字のみ）
・Tableの子要素から、Tableタグのプロパティなどを参照するには offsetparentを使用
・Ｃｌａｓｓ名を取得するにはclassNameを使用
・タグで囲まれた内側のテキストを取得するのはinnertextを使用
といった5点かと思います。