Excel VBAでYahoo路線検索を使って交通費を自動で取得する方法

このブログではアフィリエイト・アドセンス広告を利用しています

当ブログでは、アドセンス・アフィリエイト広告を掲載しています。
消費者庁が発表しているルールに沿って記事を作成していますが、問題のある表現を見つけた際にはご連絡ください。

Yahoo路線情報サイトを使って、Excelから自動で交通費を取得するシートを作成してみました。

「楽々精算・Dr.経費精算・マネーフォワードクラウド経費といった経費精算サービスの月々の利用料の負担が辛くて導入できない」という個人事業主の方や中小企業の方のお役にちょっとだけ立つかもしれません。

目次(読みたいところへジャンプ!)

    このExcel VBAで実現できること

    Yahoo路線案内を使ってExcel VBAで運賃自動取得
    このExcelファイルでできることは、シートに日付・出発駅・到着駅・片道か往復か・内容を入力した後に、「経路一括検索」ボタンを押すと、交通費が自動で設定されます。

    交通費は、無料で使用できるYahoo路線案内を利用して取得した電車のIC運賃が自動で設定されます。

    下準備

    Excel VBAで運賃を取得するファイルを作成するためには、次の下準備が必要です。

    1. Selenium Basicのダウンロードとインストール
    2. Chrome Driverのダウンロード
    3. Selenium Type Libraryの参照設定

    Selenium Basicのダウンロードとインストール

    Selenium Basicとは、VBAからChromeを簡単に操作できるようになるライブラリ(追加機能がまとめられたファイル)です。

    下のリンクから、Selenium Basicをダウンロードしてインストールをしておきましょう。

    >>>SeleniumBasic Github

    >>>seleniumBasicのダウンロード

    Chrome Driverのダウンロード

    ChromeDriverとseleniumBasicのバージョン違いエラー
    Selenium Basic内のCromeドライバーと使用中のChromeのバージョンが異なると、エラーが発生してしまいます。

    そのため、使用中のChromeのバージョンを「ヘルプ」→「Chromeについて」メニューで選択して確認しましょう。
    Chromeのバージョン確認方法

    確認できたChromeのバージョンに合うChrome Driverを下のリンクからダウンロードします。

    >>>Chrome Driverのダウンロード

    ダウンロードしたChrome DriverをインストールしたSelenium Basicフォルダ内へコピーをして、上書きしてしまえばOKです。

    Selenium Type Libraryの参照設定

    インストールしたSelenium BasicをVBAで使用できるように、Selenium Type Libraryの参照設定をしておきます。

    「開発」メニュー→「VisualBasic」→「ツール」→「参照設定」で、Selenium Type Libraryを見つけてチェックを入れれば完了です。
    ExcelVBA参照設定 Selenium

    これで、Excel VBAの中から、簡単にChromeを使ってWebスクレイピングできるようになりました。

    HYPERLINK関数で取得したい路線のURLを設定

    HYPERLINK(“URL”)

    クリックしたら、Yahoo路線案内を開くよう、F列にHYPERLINK関数を設定しておきます。

    =IF(B2="","",HYPERLINK("https://transit.yahoo.co.jp/search/result?&from="&ENCODEURL($B2)&"&to="&ENCODEURL($C2)&"&y="&TEXT(A2,"YYYY")&"&m="&TEXT(A2,"mm")&"&d="&TEXT(A2,"dd")&"&lb=1&type=1&ws=3&s=1&&fl=1&tl=3ticket=ic&expkind=1"))
    
    

    F列のURLは、日付・出発駅・到着駅セルを条件に、ルート探索したいURLが設定されるようになりました。

    Excel VBAでSelenium.ChromeDriverを使えばChromeのスクレイピングが簡単

    VBAで、ボタンがクリックされたら、F列のURLをChromeで次々に開き、片道運賃をスクレイピングで取得して、邪魔な「円」を取り除いた後に片道ならそのまま、往復なら2倍にしてE列の交通費にセットするコードを書いてみました。

    これで、ボタンがクリックされると、自動で交通費が設定される交通費精算Exelファイルが完成です。

    Sub btn_click()
    
    Dim driver As New Selenium.ChromeDriver
    Dim strValue As String
    Dim strValueAfter As String
    
    Dim intCnt As Integer
    Dim intDataCnt As Integer
    Dim intFee As Integer
    
    
    
    
    intDataCnt = Worksheets("sheet1").Range("A1").End(xlDown).Row
    
    For intCnt = 2 To intDataCnt
    '経路1の片道料金を取得
    driver.Get Worksheets("sheet1").Cells(intCnt, 6)
    ' Stop
    strValue = driver.FindElementByXPath("//*[@id=""rsltlst""]/li[1]/dl/dd/ul/li[2]").Text
    strValueAfter = Replace(strValue, "円", "")
    
    Select Case Cells(intCnt, 4).Text
    Case "往復"
    intFee = Int(strValueAfter) * 2
    Case "片道"
    intFee = Int(strValueAfter)
    Case Else
    
    End Select
    Worksheets("sheet1").Cells(intCnt, 5).Value = intFee
    Next
    
    driver.Close
    Set driver = Nothing
    
    End Sub
    
    

    Chromeではなく、IEを利用する場合は下準備が参照設定だけでOKになります。
    >>>Excelマクロでブラウザ(IE)操作。交通費をExcelから読取→サイトで検索→Excelへ書込

    今回は、Excel VBAで交通費を取得してみましたが、RPAでも同じことができます。
    >>>【UiPath】交通費検索ロボットを作ってみる

    Webスクレイピングの使用には、注意点もあります。

    Excel VBAでChromeブラウザ内をWebスクレイピングする方法と注意点

    同じカテゴリの関連記事リスト