三流君 ken3のmemo置き場

メモ置き場、保管庫として利用。まとまっていませんがヨロシク

挨拶・自己紹介:
失敗続きのAB型の変わり者 :三流プログラマー Ken3です
フリーのエンジニア・個人事業主です・・と書くと聞こえはイイが(それとなくカッコよく聞こえるが)、 現在は小さな案件の受注請負 と 短期派遣 で 日々つつましく?ほそぼそと暮らしてます。
(※詳細は[三流君 三流プログラマーとは?]を見てください)


広告:


[記事一覧、バックナンバーを見る]

VBA IE操作 表の取り込み(質問 CMEのデータ取得について)

VBA IE操作 表の取り込み(質問 CMEのデータ取得について)

下記の質問を( http://ie.vba-ken3.jp/sample/ )からいただいた。

CMEのデータ取得について
以前はCMEの日々データをWebクエリでエクセルに取り込むことが出来ました。
しかし、最近はこれが利用出来なくなりました。
このホームページでWebから直接データを取り込む方法を紹介されていましたので、
CMEからデータを取得することが可能かどうかご教示ください。

http://www.cmegroup.com/trading/equity-index/international-index/nikkei-225-dollar_quotes_settlements_futures.html

私のパターン、いつもの泥縄式でチャレンジしてみます。

IE操作の表取り込みのパターン(三流君の方針、進め方のパターン)

0.手作業で目的のWebページを表示する。
1.まず、TDで取れるか、チェックしてみる。
2.次にテーブルを探してみる、name=名前やid=xxxが付いていたらラッキー
3.見つけたテーブルのデータをセルにセットする。

簡単に書くと4行で終わりです(嘘つけ、そんなに単純じゃないくせに)

>0.手作業で目的のWebページを表示する。
操作する、取得したい表をまずは目で手で確認します。
単純に目的のページをIEで開きます。
http://www.cmegroup.com/trading/equity-index/international-index/nikkei-225-dollar_quotes_settlements_futures.html

ここで、フレームになっているのか?
テーブルの中にテーブル・・・とレイアウトでテーブル構造になっているか?
など、大まかにチェックします。
(※そのチェックが知りたいんだよみんなは、肝心な部分を書かないからなぁ)


>1.まず、TDで取れるか、チェックしてみる。
一番下のテーブルの構成要素がTDタグなので、TDタグでデータを取り込んでみます。
※THが見出しなんだけど、THは無視して(THから探った方がはやい場合もあるけど)

ここで、目的のデータが取れれば、先に進みます。
※取れなかった場合、
 ア.フレームになっていたり、
 イ.途中で読み込み直していたり、
 ウ.インラインフレーム IFRAMEタグだったり、、
 とやっかいですね。。。
そんな時は、追加でフレーム処理を入れたりします(そこの例外処理が聞きたいのに書かないつもりか今回も)

下記、チェック用で使っているプログラムです。

解説は → http://ken3-info.blog.ocn.ne.jp/objie/2009/02/217_objiedocume_09a9.html
まぁ、下記のソース、上記のテストプログラムをURLだけ変えてそのまま使用したんだけど。

Option Explicit

'IE を 起動して TD タグを抜いてシートに書いてみた
Sub test_1203_TD()

    Dim objIE    As Object  'IEオブジェクト参照用
    Set objIE = CreateObject("InternetExplorer.application") 'IEのオブジェクトを作る
    objIE.Visible = True '見えるようにする(お約束)

    '.Navigate で 指定した文字列のURLを開く
    objIE.Navigate "http://www.cmegroup.com/trading/equity-index/international-index/nikkei-225-dollar_quotes_settlements_futures.html"
    
    'ページ待ちがうまくいかなかったので、無条件で10秒待ちました。
    '↑ページを読み終わってから、再度読みに行ったりするページはやっかいですね。
    Application.Wait (Now + TimeValue("0:00:10"))  '手抜きで10秒待ち

    '.tags("TD") で TDタグを抜き出す
    Dim objTD As Object 'テーブルオブジェクトの格納用
    Set objTD = objIE.document.all.tags("TD")  '.tags("TD")でTDタグを抜く
    
    'テスト用に新規のブックを追加する
    Workbooks.Add  '新規ブックを追加
    Sheets.Add     'シートを新規追加する
    ActiveSheet.Name = "TDタグを抜くテスト"  'シートに名前を付ける
    
    'TDデータをシートに貼り付ける
    Range("A1") = "TDタグを取り出すテスト"
    
    Range("A2") = "変数n"   '見出しをつける
    Range("B2") = ".InnerHTML"   '見出しをつける
    Range("C2") = ".InnerTEXT"   '見出しをつける
    Range("D2") = ".OuterHTML"   '見出しをつける
    
    'いろいろなループを作れるけど、カウンタ n でまわしてみる
    Dim n As Integer
    For n = 0 To objTD.Length - 1  'カウンタ0から.length - 1 までまわす。
        Cells(n + 3, "A") = n   'n+3行目にセット
        Cells(n + 3, "B") = "'" & Left(objTD(n).InnerHTML, 80)  'デバック用に左から80文字までセット
        Cells(n + 3, "C") = "'" & Left(objTD(n).InnerTEXT, 80)  'デバックなのでシングルコーテーションを付け
        Cells(n + 3, "D") = "'" & Left(objTD(n).OuterHTML, 80)  '文字列扱いでセットする
    Next n

    '後始末(使った食器はキレイにしてから戸棚に戻そうね)
    Set objTD = Nothing
    
    'objIE.Quit    '今回はコメントにして処理しない(残しておいた方がテスト時は楽です)
    'Set objIE = Nothing
    
End Sub

ココまでの処理動画を入れる

http://www.youtube.com/watch?v=wf6WFymRKns



>2.次にテーブルを探してみる、name=名前やid=xxxが付いていたらラッキー

VBAから使うのはIEのオブジェクトなのですが、
ライバルのブラウザ Firefox をインストールして確認に使うと便利だよ、
と、読者の人(A.Nさんありがと)に教えてもらいました。

どんな風に便利かというと、
1.取得したい場所(今回はテーブル)近辺をマウスで選択します(ドラッグして反転させます)
2.次に右クリックを押して、選択した部分のソースを表示を選択します。

すると、選択した部分のソースを見ることができます。

これだと、ソースを頭から探さなくていいので、かなり便利です。
みなさんも一緒に A.Nさんありがとう とつぶやいてね(おぃぉぃ)

2と3の動画を貼る。

http://www.youtube.com/watch?v=nLE40iEJFss


>3.見つけたテーブルのデータをセルにセットする。
↑上の動画 後半を合わせてみてください。

ソースからテーブルのidが見つかったら、(知りたかった見つからなかったら?どうするの?は、置いておき)

id="DailySettlementTable" の オブジェクトをまず取り出します。
取り出し方は簡単で、
Set objT = objIE.document.all("DailySettlementTable") '.all("id名前")でテーブルタグを抜く
こんな感じで、
document.all("DailySettlementTable")
.all("id名前")で簡単に抜き出すことができます。

あとは、テーブルオブジェクトなので、
テーブル.Rows.Length で行の数、
行.Cells.Length で 列数
がわかるので、
yとxのループを作り、

    'Webの表をシートへ転記(代入する)
    For y = 0 To objT.Rows.Length - 1  '行のループ
        For x = 0 To objT.Rows(y).Cells.Length - 1  '列数分ループ
            Cells(y + 1 + 5, x + 1) = objT.Rows(y).Cells(x).innertext
            '↑y+1+5 6行目から書き出す、11行目にするには y+1+10に変更する
        Next
    Next
    '↑ http://ken3-info.blog.ocn.ne.jp/objie/2009/05/0317_vba_ie_9de.html を参考に。

で、セットしてみました。

Option Explicit

'IE を 起動して Table id="DailySettlementTable" からデータを抜いて
Sub test_1203_T()  'テーブルからデータを取得するテスト

    Dim objIE    As Object  'IEオブジェクト参照用
    Set objIE = CreateObject("InternetExplorer.application") 'IEのオブジェクトを作る
    objIE.Visible = True '見えるようにする(お約束)
    
    objIE.gohome  '初期ページの表示 まぁ、表示しなくてもいいんだけど。
    
    '表示終了まで待つ .Busy(忙しい)間 と.ReadyState(ステータス)が4以外の時 ループ
    Do While objIE.Busy = True Or objIE.ReadyState <> 4
         DoEvents  '特に何もしないで.Busyの状態が変わるまで待つ
    Loop
    '↑ネットの回線つなげたり、IEの初期化処理などで時間がかかるので、
    '.GoHOMEで初期ページを使わないけど表示させ、そのあと目的のページを表示させる。

    '.Navigate で 指定した文字列のURLを開く
    objIE.Navigate "http://www.cmegroup.com/trading/equity-index/international-index/nikkei-225-dollar_quotes_settlements_futures.html"
    
    'ページ待ちがうまくいかなかったので、無条件で10秒待ちました。
    '↑ページを読み終わってから、再度読みに行ったりするページはやっかいですね。
    Application.Wait (Now + TimeValue("0:00:12"))  '手抜きで12秒待ち

    'id="DailySettlementTable" の テーブルを抜き出したい

    'Tableタグを抜き出す
    Dim objT As Object 'テーブルオブジェクトの格納用
    Set objT = objIE.document.all("DailySettlementTable")  '.all("id名前")でテーブルタグを抜く
    
    If objT Is Nothing Then  '↑上で見つかったか?
        MsgBox "err 表が見つかりません、 IDを確認してください。"
        Exit Sub  'エラーなので抜ける。
    End If

    Dim x As Integer  '列の管理
    Dim y As Integer  '行の管理
    
    'Webの表をシートへ転記(代入する)
    For y = 0 To objT.Rows.Length - 1  '行のループ
        For x = 0 To objT.Rows(y).Cells.Length - 1  '列数分ループ
            Cells(y + 1 + 5, x + 1) = objT.Rows(y).Cells(x).innertext
            '↑y+1+5 6行目から書き出す、11行目にするには y+1+10に変更する
        Next
    Next
    '↑ http://ken3-info.blog.ocn.ne.jp/objie/2009/05/0317_vba_ie_9de.html を参考に。

    '後始末(使った食器はキレイにしてから戸棚に戻そうね)
    Set objT = Nothing
    
    objIE.Quit    'コメントにしてテスト時は残しておいた方が楽ですが今回は閉じる
    Set objIE = Nothing

End Sub

エラー処理や 表示待ちが手抜きですが、こんな感じで、Webページからデータを抜いてます。
もっと簡単な方法もあると思うので、
何か気が付いた人は メッセージを書き込んでいただけるとうれしいです。
※ A.N さんを越えるメッセージを待ってますよ

PS. .gohomeの小細工は、動作の速いマシーン・環境ならいらないかも。
   ある意味実戦的だけど、コードとしては無駄で汚いです。
    よく上級者から三流と私が言われる部分でもあります。

サンプルファイルは
IE_TEST_CME_GET.zip 直
を見て動かしてください。




三流君へ メッセージを送る

全ての質問に答えることはできませんが、
ダメもとで、気軽に質問、感想、メッセージを送ってくださいね・・・

感想や質問・要望・苦情など 三流君へメッセージを送る。
下記のフォームからメッセージを送ることができます。


あなたのお名前(ニックネーム):さん
返信は?:

アドレス:に返事をもらいたい
感想や質問↓:


(感想や質問・要望・苦情はHPで記事に載せることがあります。)
例:[XXXXさんへ回答例]←みたいに回答していたり...


Ken3 ホームページ 目次

分類:HPを大きく分けると4つの柱(分類)です。
・[Excel/Access VBA]の解説
・[ASP(Active Server Pages)]の解説。
・[元コンビニ店長時代の話]が弟に巻き込まれ、失敗した脱サラ、畑違い?の仕事で失敗。
・[プログラマーの愚痴]では、あまり見せたくない三流プログラマーの内面かな。
三流君を踏み台にする
主に上記4つの分類でHP作成やメルマガの発行を行ってます。
※更新頻度が落ちていて情報の鮮度が悪いです。

三流解説動画の再生リスト
https://www.youtube.com/user/ken3video/playlists

本当に三流なんです(笑):たまにスゴイですねなんて言われることもありますが、
真実は→ [三流君の真実は...] ←を初めに見てくださるとわかると思います。
(からくりは、成功例↑しか載せてなくて ヒドイ失敗例はお蔵入り迷宮入りが多かったりします)