VBAでWEBから社員の通勤距離取得
こんにちは。
今回は、Macのお話では無くWindowsのお話です。
仕事でどうしても必要となったので、ExselのVBAをしようして
必要なデータを取得しました。
自宅ではMacを使用しているのですが、仕事ではWindowsを使用してます。
たぶんそういった環境の方々は多いのではないかと思います。
今回、Excelファイルの社員住所録から会社までの直線距離と、
道なりの距離が必要となり、ネットに存在する距離計測サイトを
利用しようと考えたのですが、500件ほどのあるため1件ずつ
入力していたのでは何時終わるのかわかりません。
今回はExcelのVBAを使用して距離を計測しようと考えました。
とは言っても、VBAに精通している訳ではなく、ど素人です。
そこで、VBAの雑誌を読み、ネットを参考にしながら何とか
簡単なコードを書き、目的であった社員の自宅から会社までの
直線距離と道なりの距離を取得する事が出来ました。
Macを使用した内容ではありませんが、参考になればと思い書いて
みました。
雑誌やネットを調べてみると、緯度と経度だとかグーグルマップの
APIを利用するだとか、素人には難しい事ばかりで分かりません。
ましてそんなに時間もありませんでした。
そこで考えたのが、距離を計測出来るWEBサイトに直接住所を渡して
結果を取り込めないか？という方法です。
定型業務で使用するのでは無く、一度だけ使用するVBAなので
ファイルを開いたり閉じたりもせず、エラー処理も無く、特別な
技法もつかっていません。
一回一回、距離検索サイトのURLに社員の住所と事業所の住所を
渡し、結果をExcelファイルの社員住所横のセルに入力させています。
したがって遅いです。が、手作業で処理するよりはましです。
お昼に上がる時に実行させておけばお昼が終わるまでには終了して
いると思います。
使用させて頂いた距離検索サイトは「地図上の距離計測」です。
http://r1web.realwork.jp/index_ex.html
素人の雑なコードですが参考になればいいと思いますので載せて
おきます。
以下VBAコード
Sub add()
‘ Sheet1のB1に入力されている住所から、B2以降に入力されている住所までの
‘ 道なり距離をC2以降に、直線距離をD2以降に、webサイトを調べて入力する
‘ 一度しか使用しないので、ファイル保存もエラー処理も行っていない
Dim ie As InternetExplorer
Dim mydeta As String
Dim add1 As String
Dim add2 As String
Dim i As Integer
Range(“B1”).Activate ‘ まず、起点となる住所B1を変数add1に格納する
add1 = Selection
Range(“B2”).Activate ‘ とりあえず、最初に求める住所B2を変数add2に格納する
add2 = Selection
Range(“B1”).Activate ‘ ひとまず、セルをB1に戻しておく
i = 2 ’ セルCとC用のカウンターを2行目にしておく
mydata = “” ‘ 距離格納用変数を空にしておく
Do While add2 <> “” ‘ B列の住所が空白でない間処理を行う
add2 = “”
Set ie = CreateObject(“InternetExplorer.Application”)
ie.Visible = False
ActiveCell.Offset(1, 0).Select ‘ B列セルを一段下げる
add2 = Selection ‘ 距離を求める住所を変数add2に格納する
If add2 = “” Then GoTo owari ‘ B列が空白になったら処理終了
ie.Navigate “http://r1web.realwork.jp/index_ex.html?” + add1 + “&” + add2
‘ 起点住所と求める住所をwebサイトに渡す
Do While ie.Busy Or ie.ReadyState < READYSTATE_COMPLETE
DoEvents
Loop ‘ IEの処理終了を待つ
kilo_r:
mydata = ie.Document.getElementById(“kilo_r”).innerHTML
‘ 道なり距離を変数に格納する
If mydata = “” Then GoTo kilo_r
‘ 初心者でVBAの記述が悪いので変数に値がはいるまで待つ
Range(“C” & i).Value = mydata
‘ C列に道なり距離を代入する
‘ MsgBox mydata ‘ 確認用の記述
mydata = ie.Document.getElementById(“kilo_l”).innerHTML
‘ 変数に直線距離を格納する
Range(“D” & i).Value = mydata ‘ D列に直線距離を代入する
‘ MsgBox mydata ‘ 確認用の記述
i = i + 1 ‘ C列D列用のカウンターを1増やす
ie.Quit
‘ 初心者でVBAの記述がわからないので取りあえず一度IEを終了させる
Loop
owari:
ie.Quit ‘ IEを終了させておわり。Sheet1に書き出されたデータは保存する
End Sub
以上VBAコード
Shee1のA2以降に社員の氏名、B1に会社の住所、B2以降に社員の住所を
登録しておきます。VBAを実行すると、C2・D2以降に距離が入ります。
終了後にファイルを保存しないと取得した距離は失われますので注意！
A B C D
1 氏名 道なり距離 直線距離
2
3
4
5
以上参考になればいいのですが。