きむおばプロフィール画像

きむおばです。関東在住の40代の主婦です。 パート・派遣・フリーランスの3足のわらじ中。フリーランスでは、ファイナンシャルプランナーとしてライターやセミナー講師をしたり、サイト制作やプログラムを作ったりしています。
お金・子育て・趣味のこと…何でも気ままに書く雑記ブログです。
目標は、ライター・サイト制作・広告他の収入バランスを整えること。ブログ収入UPに今年は力を入れます。

Menu
主婦の働き方・稼ぎ方 

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

Excelの活用でもっと事務処理を時短化

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スクレイピングする方法と注意点

Excelの活用でもっと事務処理を時短化
Twitterで最新情報をお知らせ
記事がお役に立てたらシェアお願いします♪