インターネット上にデータを取得してエクセルで分析する仕事があるとき、VBAを使うと自動でデータを取得することができます。
VBAではウェブ連携のプログラムはいくつかのパターンがあります。
このページではInternet Explorerを操作して情報を取得する方法を紹介していきます。
・基本プログラムの解説
・IE操作以外のウェブ情報取得方法
それでは以下で説明していきます。
目次
- 1 「VBAでIEを操作する」とは?VBAを使ってウェブの情報を取得する事例
- 2 エクセルVBAでインターネットエクスプローラーを操作するメリット
- 3 VBAでIEに接続する前に準備しておくこと|参照設定
- 4 VBAでIE操作してウェブスクレイピング!サンプルコードを紹介
- 5 サンプルコードを詳しく解説
- 6 VBAでIEからデータ取得するときに知っておきたいこと
- 7 VBAを使いこなすには、DOM操作(getelementsbytagname/getelementsbytagidなど)を理解する
- 8 テンプレートファイルをダウンロードしたい方はコチラから
- 9 VBAでウェブ情報を取得する方法
- 10 Excel VBAについて詳しく理解したいなら
「VBAでIEを操作する」とは?VBAを使ってウェブの情報を取得する事例
「VBAでインターネットからデータを取得する」ということを知っているけれど、具体的にイメージが沸かない人は多いです。
そこで、具体的なイメージをもっていただくために、VBAを使ってウェブ情報を取得するマクロをいくつか紹介します。
事例1|エクセルの文章をGoogle翻訳で自動翻訳する
”
この動画の内容は以下のページで詳しく解説しています。
事例2|サイトのH2とH3タグをエクセルに出力する
”
この動画の内容は以下のページで詳しく解説しています。
エクセルVBAでインターネットエクスプローラーを操作するメリット
「VBAをつかってIEを操作する」ことのメリットは、以下の2つです。
エクセルVBAでIEを操作するためのプログラミングに入る前に、準備しておくことがあります。
詳しく紹介していきます。
準備1|VBEで参照設定でInternetExplorer型を追加
参照設定とは、機能拡張させることです。
Microsoft Internet Controlsにチェックを入れることで、IE操作の設定ができるようになります。
まず実際にプログラミングに入る前に、エクセルVBAでIEを操作するための参照設定でライブラリを追加する必要があります。
1.VBEを開いて頂いて、「ツール」→「参照設定」
2.この二つのライブラリにチェックを入れて、OKをクリック
・Microsoft HTML Object Library
・Microsoft Internet Controls
詳細はこちらの画像の通りです。
1.VBEを開いて頂いて、「ツール」→「参照設定」
2.この二つのライブラリにチェックを入れて、OKをクリック
・Microsoft Internet Controls
これで、ウェブ操作するマクロが動くようになります。
VBAでIE操作してウェブスクレイピング!サンプルコードを紹介
ここからは、VBAでIE操作して解析して、ウェブスクレイピングする方法をサンプルコードを合わせて紹介します。
ウェブスクレイピングとは
そもそもウェブスクレイピングとは、wikipediaによると、
ウェブサイトから情報を抽出するコンピュータソフトウェア技術のこと。(中略)ウェブスクレイピングの用途は、オンラインでの価格比較、気象データ監視、ウェブサイトの変更検出、研究、ウェブマッシュアップやウェブデータの統合等である。
引用元:wikipedia
とあります。要するに、オンラインの情報を取得してデータを利用する、ということです。言葉で説明しても、ピンとこないと思いますので、事例を使って紹介していきます。
IE操作のサンプルコード
ここでは、「ヤフオクを開いて解析をする」という事例を、コードと合わせて紹介します。具体的には、以下のことをやっていきます
コード2|インターネットの特定のページ(URL)を開く|navigate [url]で記述
コード2-1|IEを待機する関数|readystate
コード2-2|指定した秒だけ停止する関数
コード3|テキストボックスに文字を入力して検索
コード4|コード4|ボタンや「次へページへ」リンクをクリック
コード5|IEを閉じる
まずは、サンプルコードを紹介します。これをコピペして使えば、そのまま使えます。
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 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
Option Explicit Sub yahoo_auction_sample1() '---コード1|インターネットに接続してブラウザを開く--- Dim objIE As New InternetExplorer Set objIE = New InternetExplorer objIE.Visible = True '---コード2|インターネットの特定のページを開く--- objIE.Navigate "https://auctions.yahoo.co.jp/" Call IEWait(objIE) 'IEを待機 Call WaitFor(3) '3秒停止 '---コード3|IEに自動で文字入力して情報検索する--- 'キーワードを取得 Dim s As String s = "vba" Dim objtag, objsubmit As Object For Each objtag In objIE.Document.getElementsByTagName("input") If InStr(objtag.outerHTML, "type=""text") > 0 Then objtag.Value = s Exit For End If Next For Each objsubmit In objIE.Document.getElementsByTagName("input") If InStr(objsubmit.outerHTML, """検索""") > 0 Then objsubmit.Click Call WaitFor(3) Exit For End If Next '---コード4|ウェブ上のボタンを自動でクリックして次へ--- Dim objtsugi As Object For Each objtsugi In objIE.Document.getElementsByTagName("a") If InStr(objtsugi.outerHTML, "次へ") > 0 Then objtsugi.Click Call WaitFor(3) Exit For End If Next '---コード5|IEを閉じる--- objIE.Quit Set objIE = Nothing End Sub '---コード2-1|IEを待機する関数--- Function IEWait(ByRef objIE As Object) Do While objIE.Busy = True Or objIE.ReadyState <> 4 DoEvents Loop End Function '---コード2-2|指定した秒だけ停止する関数--- Function WaitFor(ByVal second As Integer) Dim futureTime As Date futureTime = DateAdd("s", second, Now) While Now < futureTime DoEvents Wend End Function |
サンプルコードを詳しく解説
上記で紹介したコードを、一つずつ丁寧に解説していきます。
コード1|インターネットに接続してブラウザを開く
1 2 3 |
Dim objIE As InternetExplorer Set objIE = New InternetExplorer objIE.Visible = True |
1行目:操作するIEを入れるオブジェクト変数(イレモノ)を準備
2行目:次にそのハコに新しいIEをセット
3行目:IEが画面表示する。まだWEBページには何も表示されていない状態
コード2|インターネットの特定のページ(URL)を開く|navigate [url]で記述
1 2 3 |
objIE.Navigate "https://auctions.yahoo.co.jp/" Call IEWait(objIE) 'IEを待機 Call WaitFor(3) '3秒停止 |
1行目:objIEをNavigate以下のURLへ誘導。なお、navigate に続くのは、文字列型なので、””で記載
2行目:IEWait(objIE)関数へ|コード2-1で解説
3行目:WaitFor(3)関数へ 3秒停止する|コード2-2で解説
ヒント:
1行目:”https://auctions.yahoo.co.jp/“を他のURLに変えることで、アクセスするウェブサイトを変更できます。
コード2-1|IEを待機する関数|readystate
1 2 3 4 5 |
Function IEWait(ByRef objIE As Object) Do While objIE.Busy = True Or objIE.ReadyState <> 4 DoEvents Loop End Function |
1行目:funcntionプロシージャの名称|()は引数を表す
2行目:busyプロパティがtrue もしくは readystateが4ではないなら、ループを繰り返す。目的はページの移動完了まで待つため。これをしておかないと、この次でエラーが発生する場合あり
3行目:ループ繰り返し
4行目:ループ繰り返し
5行目:funcntionプロシージャの終わり
コード2-2|指定した秒だけ停止する関数
1 2 3 4 5 6 7 |
Function WaitFor(ByVal second As Integer) Dim futureTime As Date futureTime = DateAdd("s", second, Now) While Now < futureTime DoEvents Wend End Function |
ちなみに、このコードでは、secondを3で受けているため、second = 3
1行目:funcntionプロシージャの名称|()は引数を表す
2行目:futuretimeをDate型で宣言
3行目:futuretimeを 今の時刻+3秒 に設定
4行目~6行目:今の時刻>futuretime(3行目を通過した時刻+3秒)となるまでループ繰り返し
7行目:funcntionプロシージャの終わり
コード3|テキストボックスに文字を入力して検索
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Dim s As String s = "vba" Dim objtag, objsubmit As Object For Each objtag In objIE.Document.getElementsByTagName("input") If InStr(objtag.outerHTML, "type=""text") > 0 Then objtag.Value = s Exit For End If Next For Each objsubmit In objIE.Document.getElementsByTagName("input") If InStr(objsubmit.outerHTML, """検索""") > 0 Then objsubmit.Click Call WaitFor(3) Exit For End If Next |
1行目:sをString型で宣言
2行目:sに”vba”を設定
3行目:objtag, objsubmitをObject型で宣言
4行目:”input”と指定されているタグを全て洗いだす
5行目:もし”input”と指定されているタグのouterHTMLに、「type=”text”」が含まれているなら
6行目:objtagにs(ここでは”vba”)を入力|このobjtagは検索ボックスをしている
7行目:Exit Forで、fornext構文から抜ける
8行目:if文の終わり
9行目:for next構文の終わり
10行目:記載なし
11行目:”input”と指定されているタグを全て洗いだす
12行目:もし”input”と指定されているタグのouterHTMLに、”検索”が含まれているなら
13行目:objsubmitをクリック|このobjsubmitは検索ボタンを指定している
14行目:WaitFor(3)関数へ 3秒停止する|コード2-2で解説
15行目:Exit Forで、fornext構文から抜ける
16行目:if文の終わり
17行目:for next構文の終わり
コード4|ボタンや「次へ」リンクをクリック
1 2 3 4 5 6 7 8 |
Dim objtsugi As Object For Each objtsugi In objIE.Document.getElementsByTagName("a") If InStr(objtsugi.outerHTML, "次へ") > 0 Then objtsugi.Click Call WaitFor(3) Exit For End If Next |
1行目:objtsugiをObject型で宣言
2行目:”a”と指定されているタグを全て洗いだす
3行目:もし”a”と指定されているタグのouterHTMLに、”次へ”が含まれているなら
4行目:objtsugiをクリック|このobjtsugiは次のページのURLリンク指定している
5行目:WaitFor(3)関数へ 3秒停止する|コード2-2で解説
6行目:Exit Forで、fornext構文から抜ける
7行目:if文の終わり
8行目:for next構文の終わり
コード5|IEを閉じる
1 2 |
objIE.quit Set objIE = Nothing |
1行目:IEのウィンドウを閉じる
2行目:生成したIEオブジェクトを破棄
ここでは、ヤフオクを事例にして紹介しました。
コード2|インターネットの特定のページ(URL)を開く|navigate [url]で記述
コード2-1|IEを待機する関数|readystate
コード2-2|指定した秒だけ停止する関数
コード3|テキストボックスに文字を入力して検索
コード4|コード4|ボタンや「次へページへ」リンクをクリック
コード5|IEを閉じる
VBAでIEからデータ取得するときに知っておきたいこと
VBAを使ってIEからデータ取得するときに知っておきたいこと、覚えておきたいことを紹介します。
ページに自動でログインする
ページへのログインですが、あまり使う機会はありません。
最近は、自動ログインされることが多く、ログイン画面への遷移されることを想定して、VBAを作っておくと、エラーのもとになります。
覚えても使わないですので、ここではあえて紹介はしません。
タブにページを追加
開いているタブにページを追加する方法を紹介します。必要であれば、コードをコピペして使ってください。
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 |
Public Const navOpenInNewTab = &H800 Sub yahoo_auction_sample2() Dim objIE As Object Dim ie As Object Set objIE = CreateObject("InternetExplorer.Application") objIE.Visible = True objIE.navigate "https://www.yahoo.co.jp/" 'Yahoo! Japanを開く Call IEWait(objIE) 'IEを待機 objIE.Navigate2 "https://auctions.yahoo.co.jp/", navOpenInNewTab'新しいタブでヤフオク!を開く Call WaitFor(5) objIE.Quit Set objIE = Nothing End Sub Function IEWait(ByRef objIE As Object) Do While objIE.Busy = True Or objIE.readyState <> 4 DoEvents Loop End Function Function WaitFor(ByVal second As Integer) Dim futureTime As Date futureTime = DateAdd("s", second, Now) While Now < futureTime DoEvents Wend End Function |
ウェブクエリの取得を使えば、マクロの自動記録も可能
ウェブクエリとはエクセルの機能の一つです。
これを利用することで、ウェブの情報を収集できます。
以下のコードはマクロの自動記録を利用して、作成したものです。
ヤフオクで検索したときの最初の情報をエクセルに出力します。
ただし、表とうまくリンクしていないので、空白セルが出てきます。
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 |
Sub webquery_yahoo_auction() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;https://auctions.yahoo.co.jp/search/search?auccat=&tab_ex=commerce&ei=utf-8&aq=-1&oq=&sc_i=&fr=auc_top&p=aiko&x=0&y=0&fixed=0", _ 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 |
2行目:https://auctions.yahoo.co.jp/search/search?auccat=&tab_ex=commerce&ei=utf-8&aq=-1&oq=&sc_i=&fr=auc_top&p=aiko&x=0&y=0&fixed=0
を他のURLに変えると、ウェブ情報を取得するサイトを変更できます。
なお、ウェブクエリなら、マクロの自動記録も反映されるので、必要なら試してみましょう。
しかし、指定したページにしか適応されないため、自動でウェブページを切り替えることはできません。
もし自動で複数ページの情報を取得したいなら、他の方法を試す必要があります。
そのためウェブ情報を自動で取得したい場合は、あまりオススメしません。
ツールバーの表示/非表示の設定方法
ツールバーの表示/非表示の設定方法を紹介します。
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 |
Sub yahoo_auction_sample3() Dim objIE As Object Set objIE = CreateObject("InternetExplorer.Application") objIE.Visible = True '---ツールバーの表示/非表示--- objIE.ToolBar = False 'ツールバーを非表示にする(もともとTrueに設定されている) objIE.AddressBar = True 'アドレスバーを表示する objIE.MenuBar = True'メニューバーを表示する '----------------------------- objIE.navigate "https://www.yahoo.co.jp/" 'Yahoo! Japanを開く Call IEWait(objIE) 'IEを待機 Call WaitFor(5) objIE.Quit Set objIE = Nothing End Sub Function IEWait(ByRef objIE As Object) Do While objIE.Busy = True Or objIE.readyState <> 4 DoEvents Loop End Function Function WaitFor(ByVal second As Integer) Dim futureTime As Date futureTime = DateAdd("s", second, Now) While Now < futureTime DoEvents Wend End Function |
オートメーションエラーが発生したら
「オートメーションエラー 例外が発生しました」というエラーが発生した場合、対策は以下の2つを試してみましょう。
2.処理回数を減らす
1.アドインの「分析ツール」を無効化
[ファイル] → [オプション] → [アドイン]と進み、分析ツールのチェックを外します。
2.処理回数を減らす
「処理が重い」、「処理回数が多い」場合、うまく読み込まれない場合があります。処理回数を減らしましょう。私の経験したことがあるので、サイト5ページ分を処理しようとすると、このエラーが出てしまいました。そこで、処理を1ページずつに変更して、問題を回避しました。
VBAを使いこなすには、DOM操作(getelementsbytagname/getelementsbytagidなど)を理解する
このページでは、VBAでヤフオクにアクセスして、商品を検索するという事例を紹介しました。
しかし実際の解析は、インターネット上の情報を取得して、以下の情報をエクセルに出力したい場合も多いです。
・価格順で並び替え
・グラフ化
上記の情報を分析するところまで出来ると、仕事の幅が広がります。
そのため、ここで紹介したコードでは、まだまだ不十分です。
ウェブ情報をスクレイピングして、情報をエクセルに書きだすところまで自力で書けるようになりたいなら、DOM操作を学ぶ必要があります。
DOMとは、(Document Object Model)のことで、html・head・body・p・aなどのHTMLドキュメント要素にアクセスして操作するための仕組みのことです。
要はプログラミングにおける、ウェブ情報を取得するための目印です。
もしVBAを使ってウェブの情報をエクセルに出力したい、解析したいなら、DOM操作を利用して、コーディングする必要があります。
このDOM操作ですが、本記事の事例で言えば、
1 |
For Each objtag In objIE.Document.getElementsByTagName("input") |
この部分で利用しています。
このコードは、「ウェブページ”input”タグに関連するhtmlテキストを全て洗い出す」操作を行っています。
このように、DOMについて理解し、DOMを活用する方法を学ぶことで、HTMLコンテンツ内の必要な箇所に簡単なコードでアクセスし、すぐにデータを取得することが可能です。
テンプレートファイルをダウンロードしたい方はコチラから
紹介したIEを操作するコードを一から作るのは大変なので、記事の中で紹介したマクロのコードが入ったファイルは無料でダウンロードできるようにします。
以下のフォームにメールアドレスを入力いただくと、返信メールからエクセルファイルをダウンロードできます。
ぜひご活用ください。
VBAでウェブ情報を取得する方法
以下でVBAを使ってウェブ情報を取得する方法を紹介します。
IE操作でブラウザからウェブ情報を取得
VBAでie操作!リンクやボタンを自動クリックしてウェブ情報を取得
VBAでヤフオクのデータを取得しExcelへ出力!IE操作マクロのテンプレート付
VBAで英語翻訳を自動化!エクセルの文章をGoogle翻訳するツール紹介
VBAでHTTPリクエストして情報を取得
VBAのHTTPリクエストでウェブページのタイトル(件名)を取得しエクセル出力
ウェブ情報を取得
VBAで天気予報の情報を取得
VBAで天気予報情報をWebAPIを通してウェブ情報を取得しエクセルへ出力
VBAで天気予報データをWebAPIで取得しJSONパースでエクセルへ出力
VBAでChatworkを操作
エクセルVBAでChatwork(チャットワーク)にメッセージを自動通知
エクセルVBAでChatworkにファイル添付とメッセージ通知を自動実行
エクセルVBAでChatworkに定期通知する仕組みを作ってみる
VBAでChatworkのタスクを全て取得してエクセルに書き出す
エクセルVBAでChatwork(チャットワーク)にタスク通知|期限、担当者も設定
VBAでChatworkのメッセージをまとめて取得してエクセルに書き出す
VBAでSlackを操作
エクセルVBAでSlackにAPIを通じてメッセージを自動通知
Excel VBAについて詳しく理解したいなら
VBAを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
動画でも解説しています。
エクセルマクロVBAで出来ることを15の事例で紹介|日常業務をラクにするヒントを見つけよう!
(音声が小さいので、ボリュームを上げてご覧いただければと思います)
VBAの勉強方法
私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。
成長の過程は以下で紹介しています。
VBAを勉強するときは、少しずつ難易度を上げて出来ることを広げていくことが大切です。
上記のリンクでは、レベルに応じたVBA勉強に役立つ内容を紹介しています。
興味がある人はご覧ください。