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

2019/8/7(水) 2019/8/7(水) きむおば

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

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

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

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

目次（読みたいところへジャンプ！）

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

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

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

下準備

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

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

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

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

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

＞＞＞SeleniumBasic Github

＞＞＞seleniumBasicのダウンロード

Chrome Driverのダウンロード

Selenium Basic内のCromeドライバーと使用中の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を見つけてチェックを入れれば完了です。

これで、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スクレイピングの使用には、注意点もあります。