ExcelマクロVBA技術解説
VBA+SeleniumBasicで検索順位チェッカー作成

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日:2019-05-19

VBA+SeleniumBasicで検索順位チェッカー作成


VBAでSeleniumBasicを使って検索順位チェッカーを作ってみます。
SEO対策として各キーワードでの検索順位チェックは欠かせませんが、
簡単に使えてキーワードを大量に指定できる良いツールがなかなかありません。



そこで、エクセルVBAでSeleniumBasicを使い、Googleの検索結果から順位を取得するVBAを作成してみました。
Seleniumについて
VBAのスクレイピングを簡単楽にしてくれるSelenium
VBAでWebスクレイピングする方法としてIE自動操作がありますが、VBA記述が結構面倒になります、もっと簡単にスマートにVBAを書きたいと思ったら…SeleniumBasicを使ってみましょう。SeleniumBasicは、エクセルVBAでのWeb閲覧を自動化するこ,VBAの問題点と解決策

今回は技術解説というより、上記記事の実践編サンプルとしての位置づけになります。


VBAコードについて特に解説が必要な部分もみあたりません。
コードだけを掲載しておきますので参考にしてください。
css selectorや、class,tagについては、chromeデベロッパーツールで確認してください。
VBA自体は難しいものではないと思います。
作成時間の多くは、対象の適切なエレメントを探す時間に費やされることになります。

シート構成

VBA マクロ スクレイピング

C1:サイトURL
A4〜:キーワード(個数制限なし)
B3:取得日
C4〜:検索結果に掲載されているページタイトル
B4〜:検索結果に掲載されているページURL
E列以降:前回以前の順位履歴

検索順位チェッカーのVBA全コード

Option Explicit

Private Driver As Selenium.WebDriver
Private Const gUrl As String = "https://www.google.co.jp/"
'検索ボックスのCSS selector
Private Const gSearch As String = "#tsf > div:nth-child(2) > div > div.RNNXgb > div > div.a4bIc > input"

Sub RankCheker()
  '起動シートの初期処理
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim siteUrl As String
  siteUrl = ws.Range("C1") 'サイトURL
  
  'Seleniumの初期処理
  Dim sKey As New Selenium.Keys
  Dim elm As Selenium.WebElement
  Set Driver = New Selenium.WebDriver
  Driver.Start "chrome"
  
  Dim i As Long
  Dim cntRank As Long
  With ws
  
    '前回実行日は前日以前なら履歴を残す
    If .Range("B3").Value < Date Then
      .Columns("E").Insert
      Intersect(.Range("A3").CurrentRegion, .Columns("B")).Copy _
        Destination:=.Range("E3")
      .Columns("E").AutoFit
    End If
    
    '出力範囲を初期クリア
    .Range("A3").CurrentRegion.Offset(1, 1).Resize(, 3).ClearContents
    For i = 4 To .Cells(.Rows.Count, 1).End(xlUp).Row
      Driver.Get gUrl
      Set elm = Driver.FindElementByCss(gSearch)
      
      '検索ボックスへキーワード送信
      elm.Clear
      elm.SendKeys .Cells(i, 1)
      elm.SendKeys sKey.Enter
      
      '検索結果からサイト掲載要素を取得
      Set elm = getElement(siteUrl, _
                .Cells(i, 1).Value, _
                cntRank)
      If Not elm Is Nothing Then
        .Cells(i, 2) = cntRank
        .Cells(i, 3) = elm.FindElementByTag("h3").Text

        .Cells(i, 4) = elm.Attribute("href")
      Else
        .Cells(i, 2) = "圏外"
      End If
    Next
    
    '実行日設定
    .Range("B3") = Date
  End With
  
  '終了処理:Close,Quitは無くても良いけど
  Driver.Close
  Driver.Quit
  Set Driver = Nothing
  MsgBox "取得完了"
End Sub

'指定selectorの要素を取得:ページ内に無い場合は「次へ」
Private Function getElement(ByVal siteUrl As String, _
              ByVal sCss As String, _
              ByRef cntRank As Long) _
              As Selenium.WebElement
  Set getElement = Nothing
  Dim elm As Selenium.WebElement
  Dim elms As Selenium.WebElements
  Dim cntPage As Long
  cntRank = 0
  On Error Resume Next
  Do
    '検索結果表示領域全体:キーワードにより表示が違う場合あり
    Set elm = Driver.FindElementByCss("#rso > div:nth-child(2) > div")
    If Err Then
      Err.Clear
      Set elm = Driver.FindElementByCss("#rso > div > div")
      If Err Then
        Exit Function
      End If
    End If
    '10件表示が<div class="g">で繰り返されている
    Set elms = elm.FindElementsByClass("g")
    If Err Then Exit Function

    For Each elm In elms
      cntRank = cntRank + 1
      '<div class="g">の中に入っているので
      Set elm = elm.FindElementByClass("r").FindElementByTag("a")
      If Err Then Exit Function
      If elm.Attribute("href") Like siteUrl & "*" Then
        Set getElement = elm
        Exit Function
      End If
    Next
    
    '100位まで
    cntPage = cntPage + 1
    If cntPage >= 10 Then
      Exit Function
    End If
    
    Driver.FindElementByLinkText("次へ").Click
    If Err Then
      Exit Function
    End If
  Loop
End Function

動作が安定しないときは、画面切り替わり直後に適宜待ち時間を入れてください。
Driver.Wait ミリ秒

最後に

掲載したVBAについては、ご自由に改変してお使いください。

実行してみると分かりますが、意外と早く取得できます。
お好みに合わせて変更し、上手く使えば結構役に立つツールにできると思います。




同じテーマ「マクロVBA技術解説」の記事

Dictionary(ディクショナリー)のパフォーマンスについて
VBAでのInternetExplorer自動操作
VBAでのSQLの基礎(SQL:Structured Query Language)
VBAで正規表現を利用する(RegExp)
VBAでメール送信する(CDO:Microsoft Collaboration Data Objects)
VBAでのOutlook自動操作
ADO(ActiveX Data Objects)の使い方の要点
特殊フォルダの取得(WScript.Shell,SpecialFolders)
参照設定、CreateObject、オブジェクト式の一覧
VBAのスクレイピングを簡単楽にしてくれるSelenium
VBA+SeleniumBasicで検索順位チェッカー作成

新着記事NEW ・・・新着記事一覧を見る

VBA+SeleniumBasicで検索順位チェッカー作成|VBA技術解説(5月18日)
テーブル操作のVBAコード(ListObject)|VBA入門(5月12日)
テーブル操作の概要(ListObject)|VBA入門(5月12日)
VBAのスクレイピングを簡単楽にしてくれるSelenium|VBA技術解説(5月6日)
Excelワークシート関数一覧(2010以降)|VBAリファレンス(4月22日)
クラスとCallByNameとポリモーフィズム(多態性)|VBA技術解説(4月6日)
VBAでのタイマー処理(SetTimer,OnTime)|VBA技術解説(4月3日)
クラスとイベントとマルチプロセス並列処理|VBA技術解説(4月2日)
エクセルの日付と時刻のまとめ|エクセル関数超技(3月6日)
Excelシートの複雑な計算式を解析するVBA|VBAサンプル集(2月18日)

アクセスランキング ・・・ ランキング一覧を見る

1.ひらがな⇔カタカナの変換|エクセル基本操作
2.最終行の取得(End,Rows.Count)|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.RangeとCellsの使い方|ExcelマクロVBA入門
6.変数とデータ型(Dim)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.繰り返し処理(For Next)|ExcelマクロVBA入門
9.とにかく書いて見よう(Sub,End Sub)|VBA入門
10.セルに文字を入れるとは(Range,Value)|VBA入門



  • >
  • >
  • >
  • VBA+SeleniumBasicで検索順位チェッカー作成

    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。






    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

    本文下部へ