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

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

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


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


Google検索をスクレイピングすることは、Google利用規約に反する可能性があります。
ここには技術解説としてVBAコードを掲載しますが、この利用を推奨しているものではありません。
掲載したVBAコードの利用については、各自のご判断でお願いします。


そこで、エクセルVBAでSeleniumBasicを使い、Googleの検索結果から順位を取得するVBAを作成してみました。
Seleniumについて

VBAのスクレイピングを簡単楽にしてくれるSelenium
VBAでWebスクレイピングする方法としてIE自動操作がありますがVBA記述が結構面倒になりますもっと簡単にスマートにVBAを書きたいと思ったら…SeleniumBasicを使ってみましょう。SeleniumBasicはエクセルVBAでのWeb閲覧を自動化することを強力かつ簡単に実現してくれます。

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


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 ミリ秒

短時間に大量の検索を行うとエラーになります。
Google検索のスクレイピングはGoogle利用規約に反する可能性もあり、
過度な検索実行は控えてください。

最後に

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

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



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

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で検索順位チェッカー作成
VBA+SeleniumBasicで検索順位チェッカー(改)


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

Byte配列と文字コード関数について|VBA技術解説(8月20日)
PowerQueryの強力な機能をVBAから利用する方法|VBA技術解説(8月4日)
練習問題31(セル結合を解除して値を埋める)|VBA練習問題(7月30日)
練習問題30(マトリックス→リスト形式)|VBA練習問題(7月25日)
Applicationを省略できるApplicationのメソッド・プロパティ一覧|VBA技術解説(7月22日)
コレクション(Collection)の並べ替え(Sort)に対応するクラス|VBA技術解説(7月20日)
CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)


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

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



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

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


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




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