・インターネット上にデータを取得して、解析する仕事がある
・自分用のインターネット連携ツールを作りたい
・今よりもっと高いITリテラシーを身につけたい
・VB以外のプログラミング言語を学ぶための、最初のきっかけをつかみたい
・「クラウド時代」に乗り遅れたくない！
そんなことを感じていませんか？
しかし、ウェブで情報を検索しても、VBAをつかって、インターネットからデータを取得する、そんな情報はあまり公開されていません。
そこで、この記事では、エクセルVBAを教えた経験がある私が、
・なかなか得ることができないVBAを使ったウェブ連携の方法
・Excel VBAを使った基本的なウェブ連携マクロを自力で書く
・ハイレベルのITリテラシーを手に入れる
これらのことを達成して、あなたのVBAのスキルの幅を広げて、ウェブから自由自在に情報を取得する方法を紹介します。
コードもあわせて紹介しますので、ぜひこのまま読みすすめていってください。
そもそもエクセルマクロVBAとは？とギモンに感じているなら、こちらの記事がオススメです。
Contents
- 1 「VBAでIEを操作する」とは？VBAを使ってamazonにアクセスし情報を取得する事例を紹介
- 2 エクセルVBAでインターネットエクスプローラーを操作するメリット
- 3 VBAでIEに接続する前に準備しておくこと｜参照設定
- 4 VBAでIE操作してウェブスクレイピング！サンプルコードを紹介
- 5 サンプルコードを詳しく解説
- 6 VBAでIEからデータ取得するときに知っておきたいこと
- 7 VBAを使いこなすには、DOM操作(getelementsbytagname/getelementsbytagidなど)を理解する
- 8 テンプレートファイルをダウンロードしたい方はコチラから
- 9 IE操作をもっと知りたい！高度なレベルにチャレンジしてみたいなら
- 10 IE操作のコードを自力で書きたい！オススメの本や無料動画
「VBAでIEを操作する」とは？VBAを使ってamazonにアクセスし情報を取得する事例を紹介
「VBAでインターネットからデータを取得する」ということを知っているけれど、具体的にイメージが沸かない…というあなたへ
具体的なイメージをもっていただくために、amazonにアクセスし情報を取得する、というマクロを紹介します。
動画で紹介するのは、「amazonのURLリンクを、自動的に自分のアカウント用のアフィリエイトリンクに修正」というものです。リンクが正しく作成されたか確認も同時にやるように設定しています。
動画デモ : アマゾンから情報を取得する : 00:33 (上の画像をクリックすると動画を再生します)
エクセルVBAでインターネットエクスプローラーを操作するメリット
「VBAをつかってIEを操作する」ことのメリットは、以下の2つです。
2.仕事を効率化のレベルが圧倒的
1.ほとんど人が使えないこと
VBAを多少かじったことがある人なら、「VBAをつかってIEを操作する」というのが、なんだか雲の上のステージ…と思われるかもしれません。
それもそのはずです。ウェブの世界では、通信があります。複数のコンピュータが登場します。HTTPというプロトコルがあり、ウェブページは、ブラウザーにより表示にクセがあります。JavaScript、CSSといった外部ライブラリの読み込みもあります。
これまでVBAをつかって、エクセルだけを操作してきたのであれば、ウェブに関することを知識ゼロから学ぶことになります。
その結果、VBA経験者のほとんどが使えない、というのが現実です。実際、VBA上級者の方が「VBAはウェブ操作が出来るレベルの人は、ほとんどいない」と言っていました。
だからこそ、IE操作を使えるようになることは、あなたの存在価値をグンと高めます。
2.仕事を効率化のレベルが圧倒的
ウェブページを1ページずつ調べて、エクセルにコピペ…
なんて作業をあなたも行ったことがあるかもしれません。
私自身、ベンチャー企業で広告費用と売上をウェブ操作して、取得するマクロを作りました。これにより、3人で15時間以上かかっていた仕事が、30分で終わるようになりました。もちろん、この30分はデータ取得の待ち時間です。(私はコーヒーを飲んで雑談をしていました)
このレベルで、仕事をするまでには、半年以上の勉強が必要でした。
しかし、ウェブ技術のエッセンスについて整理された知識を手に入れ、その視点で見通せば、決して、難しいものでもありません。
少なくとも、この記事を見れば、あなたの知識が少なくても、VBAでIE操作をするというのが、理解できてイメージできるようになります。
ぜひこのまま読みすすめていってください。
VBAでIEに接続する前に準備しておくこと｜参照設定
エクセルVBAでIEを操作するためのプログラミングに入る前に、準備しておきたいことが2つあります。
準備2｜エクセルマクロVBAを自力で編集できること
詳しく紹介していきます。
準備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 HTML Object Library
・Microsoft Internet Controls
これで、ウェブ操作するマクロが動くようになります。
準備2｜エクセルマクロVBAを自力で編集できるレベルにいること
「VBAでIE接続」を自分で編集して、ウェブスクレイピングのシステムを作れるようになるには、以下のレベルが必要です。
・Visual Basic Editor上で、自分でモジュールを追加できる
・Excel VBAについての基礎知識「オブジェクトとは何か、プロパティとは何か」といった基本知識を整理できる
・If文やFor Next構文のレベルの作業なら、何も見ないで自作できる
このレベルをクリアしていないと、IE操作のマクロを理解がおぼ付きません。このレベルが難しいと感じるなら、まずはこちらをつかって学んでみることをおすすめします。
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 |
Sub yahoo_auction_sample1() '---コード1｜インターネットに接続してブラウザを開く--- Dim objIE As InternetExplorer Set objIE = CreateObject(InternetExplorer.Application) objIE.Visible = True '---コード2｜インターネットの特定のページを開く--- objIE.Navigate http://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.<span class="synSpecial">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.<span class="synSpecial">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</span></span> |
サンプルコードを詳しく解説
上記で紹介したコードを、一つずつ丁寧に解説していきます。
コード1｜インターネットに接続してブラウザを開く
|
1 2 3 4 |
'---コード1｜インターネットに接続してブラウザを開く--- Dim objIE As InternetExplorer Set objIE = CreateObject(InternetExplorer.Application) objIE.Visible = True |
1行目：操作するIEを入れるオブジェクト変数(イレモノ)を準備
2行目：次にそのハコに新しいIEをセット
3行目：IEが画面表示する。まだWEBページには何も表示されていない状態
|
1 2 3 4 |
'---コード2｜インターネットの特定のページを開く--- objIE.Navigate http://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行目：”http://auctions.yahoo.co.jp/“を他のURLに変えることで、アクセスするウェブサイトを変更できます。
コード2-1｜IEを待機する関数｜readystate
|
1 2 3 4 5 6 |
'---コード2-1｜IEを待機する関数--- 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 8 |
'---コード2-2｜指定した秒だけ停止する関数--- 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 |
'---コード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.<span class="synSpecial">Click Call WaitFor(3) Exit For End If Next</span> |
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”を他の単語に変えることで、検索キーワードを変更できます。
コード4｜ボタンや「次へページへ」リンクをクリック
|
1 2 3 4 5 6 7 8 9 |
'---コード4｜ウェブ上のボタンを自動でクリックして次へ--- Dim objtsugi As Object For Each objtsugi In objIE.Document.getElementsByTagName(a) If InStr(objtsugi.outerHTML, 次のページ) > 0 Then objtsugi.<span class="synSpecial">Click Call WaitFor(3) Exit For End If Next</span> |
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 |
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 http://www.yahoo.co.jp/ 'Yahoo! Japanを開く Call IEWait(objIE) 'IEを待機 objIE.Navigate2 http://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 28 29 |
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 http://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でヤフオクにアクセスして、”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操作をもっと知りたい！高度なレベルにチャレンジしてみたいなら
もし、もっと具体的にIE操作について知りたいなら、こちらの記事を読んでみてください。VBAでヤフオクのデータを取得し、Excelへ出力するマクロを紹介しています。ダウンロードしてすぐに使えるテンプレート付ですので、きっとお役に立てるはずです。
また、ウェブページのリンクを自動クリックするプログラムについて、詳しく解説した記事はこちらです。ぜひ、参考にしてください。
IE操作のコードを自力で書きたい！オススメの本や無料動画
いかがでしたか？初心者必見のVBAを使ったIE操作について、事例をあわせて紹介しました。
今回の内容をぜひVBAの勉強に活かしてみてください。
と、同時に「なんだか難しそう、、IE操作は自分にはできないだろう、、、」と感じている方も多いのではないでしょうか。
たしかに、一人で学ぶとVBAでのIE操作を習得するのは、ムズカシイでしょう。
しかし、良い教材で学べば、1～2ヵ月で、ここで紹介したコードはもちろん、さらにレベルの高いコードを書けるようになります。
VBAのIE操作に関しては、なかなかいい教材はありませんが、こちらがオススメです。
1.オススメ本
しかし、本だと「分からない部分を質問できない」、「情報がアップデートされていない」ことがあります。この本も少し不十分なところがあります。ですので、がっつり学びたい方には、物足りないかもしれません。
2.ウェブ連携をマスターしたいなら
もし、IE操作のコードやITの背景の部分の知識から、完全にマスターしたいなら、こちらのオンライン動画教材がオススメです。講師に質問できますので、分からないところがあっても安心ですし、今すぐ始めることができます。
3.IE操作（ウェブ連携）より、エクセルマクロの基礎から詳しく学ぶなら
この記事を読んだけど、「私にはまだチンプンカンプン…」、「自分で編集できそうにもない…」、「マクロの基礎も理解していない…」という場合なら、IE操作を学ぶ前に、エクセルマクロの基礎から学ぶことをオススメします。エクセルからインターネットを操作するマクロは、かなりハイレベルの内容です。土台ができていない内に学ぶと、挫折の原因になってしまいます。ですので、まずは基本を学びましょう。
もしきちんと学ぶなら、こちらの無料動画がオススメです。講師に質問できますので、分からないところがあっても安心ですし、今すぐ始めることができます。
この記事がVBAでIE操作したいあなたのお役に立てれば幸いです。