パソコンスキル教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

VBAでIE操作!インターネットを開いて自動でウェブデータ取得する方法(事例とコード付)

f:id:gene320:20161231003321j:plain

・インターネット上にデータを取得して、解析する仕事がある
・自分用のインターネット連携ツールを作りたい
・今よりもっと高いITリテラシーを身につけたい
・VB以外のプログラミング言語を学ぶための、最初のきっかけをつかみたい
・「クラウド時代」に乗り遅れたくない!

そんなことを感じていませんか?

しかし、ウェブで情報を検索しても、VBAをつかって、インターネットからデータを取得する、そんな情報はあまり公開されていません。

そこで、この記事では、エクセルVBAを教えた経験がある私が、

・なかなか得ることができないVBAを使ったウェブ連携の方法
・Excel VBAを使った基本的なウェブ連携マクロを自力で書く
・ハイレベルのITリテラシーを手に入れる

これらのことを達成して、あなたのVBAのスキルの幅を広げて、ウェブから自由自在に情報を取得する方法を紹介します。

コードもあわせて紹介しますので、ぜひこのまま読みすすめていってください。

そもそもエクセルマクロVBAとは?とギモンに感じているなら、こちらの記事がオススメです。

www.fastclassinfo.com

VBAでインターネットを操作!事例を使ってデータを取得する方法を紹介

「VBAでIEを操作する」とは?VBAを使ってamazonにアクセスし情報を取得する事例を紹介

「VBAでインターネットからデータを取得する」ということを知っているけれど、具体的にイメージが沸かない…というあなたへ

具体的なイメージをもっていただくために、amazonにアクセスし情報を取得する、というマクロを紹介します。

動画で紹介するのは、「amazonのURLリンクを、自動的に自分のアカウント用のアフィリエイトリンクに修正」というものです。リンクが正しく作成されたか確認も同時にやるように設定しています。


動画デモ : アマゾンから情報を取得する : 00:33 (上の画像をクリックすると動画を再生します)

エクセルVBAでインターネットエクスプローラーを操作するメリット

「VBAをつかってIEを操作する」ことのメリットは、以下の2つです。
1.ほとんど人が使えないこと
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つあります。

準備1|VBEで参照設定でInternetExplorer型を追加
準備2|エクセルマクロVBAを自力で編集できること

詳しく紹介していきます。

準備1|VBEで参照設定でInternetExplorer型を追加

参照設定とは、機能拡張させること、です。

Microsoft Internet Controlsにチェックを入れることで、IE操作の設定ができるようになります。

まず実際にプログラミングに入る前に、エクセルVBAでIEを操作するための参照設定でライブラリを追加する必要があります。

設定方法

1.VBEを開いて頂いて、「ツール」→「参照設定」
2.この二つのライブラリにチェックを入れて、OKをクリック
・Microsoft HTML Object Library
・Microsoft Internet Controls

詳細はこちらの画像の通りです。

1.VBEを開いて頂いて、「ツール」→「参照設定」

f:id:gene320:20170613213650p:plain

2.この二つのライブラリにチェックを入れて、OKをクリック
・Microsoft HTML Object Library
・Microsoft Internet Controls

f:id:gene320:20170613213737p:plain

これで、ウェブ操作するマクロが動くようになります。

準備2|エクセルマクロVBAを自力で編集できるレベルにいること

「VBAでIE接続」を自分で編集して、ウェブスクレイピングのシステムを作れるようになるには、以下のレベルが必要です。

VBAでIE接続を学ぶ推奨レベル

・Visual Basic Editor上で、自分でモジュールを追加できる
・Excel VBAについての基礎知識「オブジェクトとは何か、プロパティとは何か」といった基本知識を整理できる
・If文やFor Next構文のレベルの作業なら、何も見ないで自作できる

このレベルをクリアしていないと、IE操作のマクロを理解がおぼ付きません。

このレベルにいない方は、まずはこちらをつかって学んでみることをおすすめします。

エクセルVBA達人塾(無料オンライン動画)

VBAでIE操作して解析し、ウェブスクレイピング|サンプルコードで分かりやすく解説

ここからは、VBAでIE操作して解析、ウェブスクレイピングする方法をサンプルコードを合わせて紹介します。

ウェブスクレイピングとは

そもそもウェブスクレイピングとは、wikipediaによると、

ウェブサイトから情報を抽出するコンピュータソフトウェア技術のこと。(中略)ウェブスクレイピングの用途は、オンラインでの価格比較、気象データ監視、ウェブサイトの変更検出、研究、ウェブマッシュアップやウェブデータの統合等である。
引用元:wikipedia

とあります。要するに、オンラインの情報を取得してデータを利用する、ということです。言葉で説明しても、ピンとこないと思いますので、事例を使って紹介していきます。

ここでは、「ヤフオクを開いて解析をする」という事例を、コードと合わせて紹介します。具体的には、以下のことをやっていきます

コード1|インターネットに接続してブラウザを開く
コード2|インターネットの特定のページを開く
コード2-1|IEを待機する関数
コード2-2|指定した秒だけ停止する関数
コード3|IEに自動で文字入力して情報検索する
コード4|ウェブ上のボタンを自動でクリックして次へ
コード5|IEを閉じる

まずは、サンプルコードを紹介します。これをコピペして使えば、そのまま使えます。

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.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|インターネットに接続してブラウザを開く

    Dim objIE As InternetExplorer
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Visible = True

1行目:操作するIEを入れるオブジェクト変数(イレモノ)を準備
2行目:次にそのハコに新しいIEをセット
3行目:IEが画面表示する。まだWEBページには何も表示されていない状態

コード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を待機する関数

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|指定した秒だけ停止する関数

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|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

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|ウェブ上のボタンを自動でクリックして次へページへ

    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を閉じる

    objIE.quit
    Set objIE = Nothing

1行目:IEのウィンドウを閉じる
2行目:生成したIEオブジェクトを破棄

ここでは、ヤフオクを事例に、

コード1|インターネットに接続してブラウザを開く
コード2|インターネットの特定のページを開く
コード2-1|IEを待機する関数
コード2-2|指定した秒だけ停止する関数
コード3|IEに自動で文字入力して情報検索する
コード4|ウェブ上のボタンを自動でクリックして次へ
コード5|IEを閉じる

詳しく説明しました。ぜひコピペしてぜひ活用してみてください。

VBAでIEからデータ取得するときに知っておきたいこと

VBAを使ってIEからデータ取得するときに知っておきたいこと、覚えておきたいことを紹介します。

ページに自動でログインする

ページへのログインですが、あまり使う機会はありません。

最近は、自動ログインされることが多く、ログイン画面への遷移されることを想定して、VBAを作っておくと、エラーのもとになります。

覚えても使わないですので、ここではあえて紹介はしません。

タブにページを追加

開いているタブにページを追加する方法を紹介します。必要であれば、コードをコピペして使ってください。

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

ウェブクエリの取得方法

ウェブクエリとはエクセルの機能の一つです。

f:id:gene320:20170613221616p:plain

これを利用することで、ウェブの情報を収集できます。

以下のコードはマクロの自動記録を利用して、作成したものです。 ヤフオクで「aiko」と検索したときの最初の情報をエクセルに出力します。ただし、表とうまくリンクしていないので、空白セルが出てきます。

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に変えると、ウェブ情報を取得するサイトを変更できます。

このマクロを使えば、ウェブ情報をエクセルに出力できます。しかし、指定したページにしか適応されないため、自動でウェブページを切り替えることはできません。もし、自動で複数ページの情報を取得したいなら、他の方法を試す必要があります。

ですので、ウェブ情報を自動で取得したい場合は、あまりオススメしません。

VBAでウェブスクレイピングするにはDOM操作を理解する

この記事では、VBAでヤフオクにアクセスして、"aiko"とつく商品を検索するという事例を紹介しました。

しかし、実際の解析は、インターネット上の情報を取得して、情報をエクセルに出力して

・日付で並び替えたり
・価格順で並べたり
・グラフにしたり

情報を分析するところまでやりたいですよね。

ですので、ここで紹介したコードでは、まだまだ不十分です。ウェブ情報をスクレイピングして、情報をエクセルに書きだすところまで自力で書けるようになりたいなら、DOM操作を学ぶ必要があります。

DOMとは、(Document Object Model)のことで、html・head・body・p・aなどのHTMLドキュメント要素にアクセスして操作するための仕組みのことです。

カンタンに言えば、VBAを使って、ウェブ情報を取得するための目印です。

もしあなたが、VBAを使ってウェブの情報をエクセルに出力したい、解析したいなら、DOM操作を利用して、コーディングする必要があります。

このDOM操作ですが、本記事の事例で言えば、

    For Each objtag In objIE.Document.getElementsByTagName("input")

この部分で利用しています。

このコードは、「ウェブページ"input"タグに関連するhtmlテキストを全て洗い出す」操作を行っています。

このように、DOMについて理解し、DOMを活用する方法を学ぶことで、HTMLコンテンツ内の必要な箇所に簡単なコードでアクセスし、すぐにデータを取得することが可能です。

IE操作を具体例で知りたいならコチラ!VBAでヤフオクのデータを取得しExcelへ出力する方法

もし、もっと具体的にIE操作について知りたいなら、こちらの記事を読んでみてください。VBAでヤフオクのデータを取得し、Excelへ出力するマクロを紹介しています。ダウンロードしてすぐに使えるテンプレート付ですので、きっとお役に立てるはずです。

www.fastclassinfo.com

IE操作のコードを自力で書けるようになりたいなら

いかがでしたか?初心者必見のVBAを使ったIE操作について、事例をあわせて紹介しました。

今回の内容をぜひVBAの勉強に活かしてみてください。

と、同時に「なんだか難しそう、、IE操作は自分にはできないだろう、、、」と感じている方も多いのではないでしょうか。

たしかに、一人で学ぶとVBAでのIE操作を習得するのは、ムズカシイでしょう。

しかし、良い教材で学べば、1~2ヵ月で、ここで紹介したコードはもちろん、さらにレベルの高いコードを書けるようになります。

VBAのIE操作に関しては、なかなかいい教材はありませんが、こちらがオススメです。

ウェブ連携の事例を合わせてもう少し知りたいなら、本がオススメ

Excel VBAでIEを思いのままに操作できるプログラミング術 Excel 2013/2010/2007/2003対応

Excel VBAでIEを思いのままに操作できるプログラミング術 Excel 2013/2010/2007/2003対応

しかし、本だと「分からない部分を質問できない」、「情報がアップデートされていない」ことがあります。この本も少し不十分なところがあります。ですので、がっつり学びたい方には、物足りないかもしれません。

ウェブ連携をマスターしたいなら、オンライン動画教材がおすすめ

もし、IE操作のコードやITの背景の部分の知識から、完全にマスターしたいなら、こちらのオンライン動画教材がオススメです。講師に質問できますので、分からないところがあっても安心ですし、今すぐ始めることができます。

この記事がVBAでIE操作したいあなたのお役に立てれば幸いです。