仕事ができるようになるスキルをお届け
そんなことを感じていませんか？
しかし、ウェブで情報を検索しても、VBAをつかって、インターネットからデータを取得する、そんな情報はあまり公開されていません。
そこで、この記事では、エクセルVBAを教えた経験がある私が、
・なかなか得ることができないVBAを使ったウェブ連携の方法
・Excel VBAを使った基本的なウェブ連携マクロを自力で書く
・ハイレベルのITリテラシーを手に入れる
これらのことを達成して、あなたのVBAのスキルの幅を広げて、ウェブから自由自在に情報を取得する方法を紹介します。
コードもあわせて紹介しますので、ぜひこのまま読みすすめていってください。
そもそもエクセルマクロVBAとは？とギモンに感じているなら、こちらの記事がオススメです。
Contents
「VBAでインターネットからデータを取得する」ということを知っているけれど、具体的にイメージが沸かない人は多いです。
そこで、具体的なイメージをもっていただくために、VBAを使ってウェブ情報を取得するマクロをいくつか紹介します。
”
この動画の内容は以下の記事で詳しく解説しています。
”
この動画の内容は以下の記事で詳しく解説しています。
以下の動画で紹介するのは、「amazonのURLリンクを、自動的に自分のアカウント用のアフィリエイトリンクに修正」というものです。
アフィリエイトリンクが正しく作成されたか確認も同時にやるように設定しています。
”
上の画像をクリックすると動画を再生します
「VBAをつかってIEを操作する」ことのメリットは、以下の2つです。
エクセルVBAでIEを操作するためのプログラミングに入る前に、準備しておきたいことが2つあります。
詳しく紹介していきます。
参照設定とは、機能拡張させること、です。
Microsoft Internet Controlsにチェックを入れることで、IE操作の設定ができるようになります。
まず実際にプログラミングに入る前に、エクセルVBAでIEを操作するための参照設定でライブラリを追加する必要があります。
詳細はこちらの画像の通りです。
1.VBEを開いて頂いて、「ツール」→「参照設定」
2.この二つのライブラリにチェックを入れて、OKをクリック
・Microsoft HTML Object Library
・Microsoft Internet Controls
これで、ウェブ操作するマクロが動くようになります。
「VBAでIE接続」を自分で編集して、ウェブスクレイピングのシステムを作れるようになるには、以下のレベルが必要です。
このレベルをクリアしていないと、IE操作のマクロを理解がおぼ付きません。このレベルが難しいと感じるなら、まずはこちらをつかって学んでみることをおすすめします。
ここからは、VBAでIE操作して解析して、ウェブスクレイピングする方法をサンプルコードを合わせて紹介します。
そもそもウェブスクレイピングとは、wikipediaによると、
ウェブサイトから情報を抽出するコンピュータソフトウェア技術のこと。(中略)ウェブスクレイピングの用途は、オンラインでの価格比較、気象データ監視、ウェブサイトの変更検出、研究、ウェブマッシュアップやウェブデータの統合等である。
引用元：wikipedia
とあります。要するに、オンラインの情報を取得してデータを利用する、ということです。言葉で説明しても、ピンとこないと思いますので、事例を使って紹介していきます。
ここでは、「ヤフオクを開いて解析をする」という事例を、コードと合わせて紹介します。具体的には、以下のことをやっていきます
まずは、サンプルコードを紹介します。これをコピペして使えば、そのまま使えます。
|
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 |
Sub yahoo_auction_sample1() '---コード1｜インターネットに接続してブラウザを開く--- Dim objIE As InternetExplorer Set objIE = CreateObject("InternetExplorer.Application") 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 = "aiko" Dim objtag, objsubmit As Object For Each objtag In objIE.Document.getElementsByTagName("input") If InStr(objtag.outerHTML, """yschsp""") > 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 2 3 |
Dim objIE As InternetExplorer Set objIE = CreateObject("InternetExplorer.Application") objIE.Visible = True |
1行目：操作するIEを入れるオブジェクト変数(イレモノ)を準備
2行目：次にそのハコに新しいIEをセット
3行目：IEが画面表示する。まだWEBページには何も表示されていない状態
|
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に変えることで、アクセスするウェブサイトを変更できます。
|
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プロシージャの終わり
|
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プロシージャの終わり
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Dim s As String s = "aiko" Dim objtag, objsubmit As Object For Each objtag In objIE.Document.getElementsByTagName("input") If InStr(objtag.outerHTML, """yschsp""") > 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に”aiko”を設定
3行目：objtag, objsubmitをObject型で宣言
4行目：”input”と指定されているタグを全て洗いだす
5行目：もし”input”と指定されているタグのouterHTMLに、”yschsp”が含まれているなら
6行目：objtagにs(ここでは”aiko”)を入力｜この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構文の終わり
ヒント：
2行目：”aiko”を他の単語に変えることで、検索キーワードを変更できます。
|
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構文の終わり
|
1 2 |
objIE.quit Set objIE = Nothing |
1行目：IEのウィンドウを閉じる
2行目：生成した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 |
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 |
ウェブクエリとはエクセルの機能の一つです。
これを利用することで、ウェブの情報を収集できます。
以下のコードはマクロの自動記録を利用して、作成したものです。
ヤフオクで「aiko」と検索したときの最初の情報をエクセルに出力します。ただし、表とうまくリンクしていないので、空白セルが出てきます。
|
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 |
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つを試してみましょう。
1.アドインの「分析ツール」を無効化
[ファイル] → [オプション] → [アドイン]と進み、分析ツールのチェックを外します。
2.処理回数を減らす
「処理が重い」、「処理回数が多い」場合、うまく読み込まれない場合があります。処理回数を減らしましょう。私の経験したことがあるので、サイト5ページ分を処理しようとすると、このエラーが出てしまいました。そこで、処理を1ページずつに変更して、問題を回避しました。
この記事では、VBAでヤフオクにアクセスして、”aiko”とつく商品を検索するという事例を紹介しました。
しかし、実際の解析は、インターネット上の情報を取得して、情報をエクセルに出力して
・日付で並び替えたり
・価格順で並べたり
・グラフにしたり
情報を分析するところまでやりたいですよね。
ですので、ここで紹介したコードでは、まだまだ不十分です。ウェブ情報をスクレイピングして、情報をエクセルに書きだすところまで自力で書けるようになりたいなら、DOM操作を学ぶ必要があります。
DOMとは、(Document Object Model)のことで、html・head・body・p・aなどのHTMLドキュメント要素にアクセスして操作するための仕組みのことです。
カンタンに言えば、VBAを使って、ウェブ情報を取得するための目印です。
もしあなたが、VBAを使ってウェブの情報をエクセルに出力したい、解析したいなら、DOM操作を利用して、コーディングする必要があります。
このDOM操作ですが、本記事の事例で言えば、
|
1 |
For Each objtag In objIE.Document.getElementsByTagName("input") |
この部分で利用しています。
このコードは、「ウェブページ”input”タグに関連するhtmlテキストを全て洗い出す」操作を行っています。
このように、DOMについて理解し、DOMを活用する方法を学ぶことで、HTMLコンテンツ内の必要な箇所に簡単なコードでアクセスし、すぐにデータを取得することが可能です。
紹介したIEを操作するコードを一から作るのは大変なので、記事の中で紹介したマクロのコードが入ったファイルは無料でダウンロードできるようにします。以下のフォームにメールアドレスを入力いただくと、返信メールからエクセルファイルをダウンロードできます。
ぜひご活用ください。
もし、もっと具体的にIE操作について知りたいなら、こちらの記事を読んでみてください。VBAでヤフオクのデータを取得し、Excelへ出力するマクロを紹介しています。ダウンロードしてすぐに使えるテンプレート付ですので、きっとお役に立てるはずです。
また、ウェブページのリンクを自動クリックするプログラムについて、詳しく解説した記事はこちらです。ぜひ、参考にしてください。
以下の記事も参考になるはずです。
いかがでしたか？初心者必見のVBAを使ったIE操作について、事例をあわせて紹介しました。
今回の内容をぜひVBAの勉強に活かしてみてください。
と、同時に「なんだか難しそう、、IE操作は自分にはできないだろう、、、」と感じている方も多いのではないでしょうか。
たしかに、一人で学ぶとVBAでのIE操作を習得するのは、ムズカシイでしょう。
しかし、良い教材で学べば、1～2ヵ月で、ここで紹介したコードはもちろん、さらにレベルの高いコードを書けるようになります。
VBAのIE操作に関しては、なかなかいい教材はありませんが、こちらがオススメです。
1.オススメ本
しかし、本だと「分からない部分を質問できない」、「情報がアップデートされていない」ことがあります。この本も少し不十分なところがあります。ですので、がっつり学びたい方には、物足りないかもしれません。
2.ウェブ連携をマスターしたいなら
もし、IE操作のコードやITの背景の部分の知識から、完全にマスターしたいなら、こちらのオンライン動画教材がオススメです。講師に質問できますので、分からないところがあっても安心ですし、今すぐ始めることができます。
3.IE操作（ウェブ連携）より、エクセルマクロの基礎から詳しく学ぶなら
この記事を読んだけど、「私にはまだチンプンカンプン…」、「自分で編集できそうにもない…」、「マクロの基礎も理解していない…」という場合なら、IE操作を学ぶ前に、エクセルマクロの基礎から学ぶことをオススメします。エクセルからインターネットを操作するマクロは、かなりハイレベルの内容です。土台ができていない内に学ぶと、挫折の原因になってしまいます。ですので、まずは基本を学びましょう。
もしきちんと学ぶなら、こちらの無料動画がオススメです。講師に質問できますので、分からないところがあっても安心ですし、今すぐ始めることができます。
この記事がVBAでIE操作したいあなたのお役に立てれば幸いです。
