WEBページの検索画面で商品の品番を検索し商品ページを印刷する(Excel VBA)

16413 ワード
ExcelVBA WebScraping ExcelVBA テキストリンク

はじめに

とあるWEBページの検索窓に商品の品番を入力し検索を実行、表示された商品ページを印刷、次に別の商品の品番を検索しその商品ページを印刷・・・これをひたすら繰り返す仕事があり、自動化しました。今回はエクセルVBAを使用しました。

やること

コードを書く前にやることをまとめます。

1. エクセルシートに検索したい商品の品番と商品名をリスト化しておく
2. WEBのトップページに移動
3. 検索窓に品番を入力し、検索ボタンを押す
4. 商品ページへのリンクが表示されるのでリンクをクリック
5. 商品ページが表示されるので画面を印刷
6. 商品ページにも検索窓があるのでここに次の品番を入力し、検索ボタンを押す
7. 4.～6.を繰り返す

エクセルシートのテーブル

まず、1.です。こんな感じのリストを作成しておきます。印刷が完了したら「完了」の文字を表示する欄をC列に設けます。

完成コード

完成したコードは以下の通りです。

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub search_goods()

Dim ie As Object
Dim i As Integer
Dim link_url As HTMLAnchorElement

'WEBページのトップページのURL
Const toppage = "https://***"

    Range(Cells(2, 3), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 3)).ClearContents

    Set ie = CreateObject("InternetExplorer.Application")

    ie.Visible = False

    ie.Navigate toppage

    Do While ie.readyState <> 4
        Do While ie.Busy = True
        Loop
    Loop

    Sleep 1000

    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row

        ie.document.getElementById("edit-search-block-form-1").Value = Cells(i, 2).Value

        ie.document.getElementById("edit-submit").Click

        Do While ie.readyState <> 4
            Do While ie.Busy = True
            Loop
        Loop

        Sleep 3000


        For Each link_url In ie.document.getElementsByTagName("a")
            If link_url.innerText = Cells(i, 1).Value Then
                link_url.Click
            End If
        Next link_url

        Do While ie.readyState <> 4
                Do While ie.Busy = True
                Loop
            Loop

        Sleep 3000

        ie.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER
        Cells(i, 3).Value = "完了"

        Sleep 1000

    Next i

    ie.Quit
    Set ie = Nothing

    MsgBox "完了しました"

End Sub

解説

さきほども説明しましたが、印刷が終わったらC列に「完了」表示をさせているので、このツールを何度も実行する場合に備えまずその「完了」の文字を消します。

    Range(Cells(2, 3), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 3)).ClearContents



次に、CreateObject関数で外部アプリケーションであるInternetExplorerを使います。定数toppageにトップページのURLが入っているので、Navigateメソッドを使ってトップページを開きます(Visible = FalseとすることでIEを非表示にしておきます)。

    Set ie = CreateObject("InternetExplorer.Application")

    ie.Visible = False

    ie.Navigate toppage



　WEBページのスクレイピングで大事なのは、ページがきちんと表示されてから次の動作を行うことです。
そのためには必要に応じてプログラムを待機させることが必要です。readyStateプロパティは、IEの読み込み状態を表し、0～4の整数値を取ります。「4」は全データ読み込み完了状態を表し、オブジェクトがこの状態にない間ループさせることで待機させます。
　また、Busyプロパティは、WEBページが読み込み状態かどうかを表し、True：読み込み中、False：読み込み完了を表します。読み込み中の間ループさせることで待機させます。
今回は、さらにSleep関数を使って、１秒待機させることでうまく動きました(この辺の塩梅が難しいです・・)。ちなみにSleep関数は、Windows API関数のため、コードの頭にPrivate Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)と記述しておく必要があります。

    Do While ie.readyState <> 4
        Do While ie.Busy = True
        Loop
    Loop

    Sleep 1000



「やること」で示した3.～7.はループ処理です。For文で行います。
今回の場合、トップページの検索窓と商品ページにある検索窓のidが一緒(edit-search-block-form-1)で、検索ボタンのidも一緒(edit-submit)だったので簡単なFor文でループ処理が記述できます。(といってもWEBページを作るとき普通idは一緒にするのかもしれませんが・・)


For文でセルの2行目からテーブルの一番下までループさせます。
getElementByIdメソッドで、idが「edit-search-block-form-1」である要素(検索窓)を取得し、そこにエクセルシート上に入力された品番の値を入れます。
同様に、getElementByIdメソッドで、idが「edit-submit」である要素(検索ボタン)を取得し、Clickメソッドを使って検索ボタンをクリックします。

先ほどと同様に待機を行いますが、ここは3秒くらい待機させないとうまくいきませんでした。

    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row

        ie.document.getElementById("edit-search-block-form-1").Value = Cells(i, 2).Value

        ie.document.getElementById("edit-submit").Click

        Do While ie.readyState <> 4
            Do While ie.Busy = True
            Loop
        Loop

        Sleep 3000



上記までの操作で商品検索結果のページが表示されます。ここから該当する商品のページに移動する必要があり、そのためには商品名が記載されたリンクをクリックする必要があります。
ということで、以下のコードのようにgetElementsByTagNameメソッドでページ中のあらゆるa要素を集め、そのうち要素内容が商品名のリンクをクリックします。

For Each link_url In ie.document.getElementsByTagName("a")
            If link_url.innerText = Cells(i, 1).Value Then
                link_url.Click
            End If
        Next link_url

        Do While ie.readyState <> 4
                Do While ie.Busy = True
                Loop
            Loop

        Sleep 3000



商品ページに移動したらページを印刷します。印刷にはExecWBメソッドを使用します。
こちらのサイトが大変参考になりました。
VBAによるIE自動制御 画面を印刷する

ExecWBメソッドはIEのコマンドを実行するメソッドです。一番目の引数には実行したい内容のコマンドを指定し、二番目の引数にはオプションを指定します。

とのことなので、印刷には第一引数をOLECMDID_PRINTとし、勝手に印刷してほしいので(いちいちプレビューを表示させずに印刷したい)第二引数にはOLECMDEXECOPT_DONTPROMPTUSERを指定します。印刷後、エクセルシートのC列に「完了」と表記します。
この後1秒待機させないと一番最後の商品だけ印刷できなかったのでSleep 1000を加えてあります。

        ie.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER
        Cells(i, 3).Value = "完了"

        Sleep 1000



ループを抜けたらQuitメソッドでIEを閉じ、Set ie = Nothingでオブジェクト変数を解除します。

    ie.Quit
    Set ie = Nothing

    MsgBox "完了しました"

まとめ

いたるところにSleepを入れてあげないとうまく動かない場合があり(商品の一部だけ印刷できない)その辺は試行錯誤が必要なようです。
今回のツールを応用すすることで様々なサイトから情報を入手できるようになりそうです。