VBAで住所から郵便番号を取得してみる（Google Maps Geocoding API版）

前回のエントリーではGoogle MapのGeocoding APIを使って緯度・経度を取得しましたが今回はタイトル通り住所から郵便番号を取得するVBAコードをアップします。

以下のよう住所入力用の欄と実行ボタンを配置します。今回は住所が複数でも対応可能にしました。

【要注意】Google Maps Geocoding API のポリシーと使用制限

今回のケースはまとめて郵便番号を検索したい場合に便利で実用的なサンプルコードですがGoogle Maps Geocoding APIは実際にGoogleマップに結果を表示するときにのみ併用で使えるもので、それ以外の利用はポリシーで禁止されています。
あくまでGoogle Maps Geocoding APIの使い方やxmlファイルのVBA操作の参考程度でご利用下さい。

また、Google Maps Geocoding APIには使用制限があります。無料で使えるのは1日に2,500回または1秒に50回のリクエストまで。
このリクエスト数を超えた場合は従量制で課金されることになりますのでご注意下さい！詳しくは公式サイトをご確認下さい。

1.Google Maps Geocoding APIの使い方

今回のジオコーディングはxml形式でGoogle Maps Geocoding APIにリクエストします。
以下の内容でリクエストします。

http://maps.googleapis.com/maps/api/geocode/xml?address=東京都中央区日本橋

Geocoding APIから返ってくるxml

上記のリクエストを実行すると以下のxmlを取得します。
「result」の子要素「address_component」の中に郵便番号が含まれている事がわかります。
ただ「address_component」要素は複数繰り返されているので、その中でも「type」要素に文字列”postal_code”が含まれているものを特定する必要があります。

<GeocodeResponse>
 <status>OK</status>
 <result>
  <type>political</type>
  <type>sublocality</type>
  <type>sublocality_level_1</type>
  <formatted_address>日本、〒103-0027 東京都中央区日本橋</formatted_address>
  <address_component>
   <long_name>日本橋</long_name>
   <short_name>日本橋</short_name>
   <type>political</type>
   <type>sublocality</type>
   <type>sublocality_level_1</type>
  </address_component>
  <address_component>
   <long_name>中央区</long_name>
   <short_name>中央区</short_name>
   <type>locality</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>東京都</long_name>
   <short_name>東京都</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>日本</long_name>
   <short_name>JP</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>103-0027</long_name>
   <short_name>103-0027</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>35.6803660</lat>
    <lng>139.7716695</lng>
   </location>
   <location_type>APPROXIMATE</location_type>
   <viewport>
    <southwest>
     <lat>35.6780347</lat>
     <lng>139.7704029</lng>
    </southwest>
    <northeast>
     <lat>35.6844046</lat>
     <lng>139.7786750</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>35.6780347</lat>
     <lng>139.7704029</lng>
    </southwest>
    <northeast>
     <lat>35.6844046</lat>
     <lng>139.7786750</lng>
    </northeast>
   </bounds>
  </geometry>
  <place_id>ChIJU_JmlleJGGARa7vrQMGllR4</place_id>
 </result>
</GeocodeResponse>

2.VBAサンプルコード

実際にExcelに組み込むサンプルコードを紹介します。

ジオコード実行ボタンに割り当てるコード

Public Sub GeoCode()

Dim RowNo   As Long
Dim i       As Long
    
'最終行を取得
RowNo = Cells(1, 1).End(xlDown).Row

'最終行までループ処理する
For i = 2 To RowNo
    '住所が入力されていたらジオコード処理を実行
    If ActiveSheet.Cells(i, 1).Value <> "" Then
        ActiveSheet.Cells(i, 2).Value = GeoCoding_zip(ActiveSheet.Cells(i, 1).Value)
    End If
Next i

End Sub

■VBAコードの補足

最終行を取得：7行目
住所欄（A列）の最終行を取得します。連続した入力の最終行になりますので途中で空白があった場合はそこで処理が終了しますのでご注意下さい。
結果をExcelシートに表示：13行目
住所をGeoCoding_zipに渡しジオコーディングを実行して郵便番号を取得します。

冒頭でも書きましたがGoogle Maps Geocoding APIには使用制限があります。このループ処理では1秒に50回まで処理スピードを落とすと言う配慮は全くありません。50行以上住所を入力した場合はこの使用制限に引っかかる可能性がありますのでくれぐれも自己責任でご利用下さい。

ジオコード結果（xml）から郵便番号を取得する

Function GeoCoding_zip(ByVal adress As String) As String
'GoogleMaps API XML形式でジオコードを取得
'戻り値：郵便番号(long_name)

Dim HttpReq         As MSXML2.XMLHTTP60
Dim DomDoc          As MSXML2.DOMDocument60
Dim strGeocode      As String
Dim xmlresult       As IXMLDOMNode
Dim xmlZip          As IXMLDOMNode
Dim xmlStatus       As IXMLDOMNode
Dim URL             As String
Dim wCount          As Long

'Google Maps Geocoding API
URL = "https://maps.googleapis.com/maps/api/geocode/xml?address=" & Encode_Uni2UTF(adress)

'XMLHTTPオブジェクトをセット
Set HttpReq = New MSXML2.XMLHTTP60

With HttpReq
    .Open "GET", URL, varAsync:=False           '非同期モードで通信を開始
    .send                                       'リクエストを送信
    If .Status <> 200 Then Exit Function        'リクエストが成功しなかったら終了
    Set DomDoc = New MSXML2.DOMDocument60
End With

'XMLから情報を抽出する
With DomDoc
    
    'XMLドキュメントを読み込む
    .LoadXML (HttpReq.responseText)
    
    'resultの件数をカウントする
    Set xmlresult = .SelectSingleNode("//GeocodeResponse")
    wCount = 0
    For Each xmlresult In xmlresult.ChildNodes
        If xmlresult.nodeName = "result" Then
            wCount = wCount + 1
        End If
    Next
    
    '複数の結果が返ってきた場合
    If wCount >= 2 Then
        strGeocode = "住所を確認して下さい。結果が複数あります。"
        GoSub End_GeoCode
    End If
    
    'status要素を取得
    Set xmlStatus = .SelectSingleNode("//GeocodeResponse/status")
    
    'ステータスの状態をチェック
    Select Case xmlStatus.Text
    
        'ジオコード成功の場合
        Case "OK"
            '郵便番号を取得
            Set xmlresult = .SelectSingleNode("//GeocodeResponse/result")
            For Each xmlresult In xmlresult.ChildNodes
                If xmlresult.nodeName = "address_component" Then
                    '3番目の子要素（type）がpostal_codeかチェック
                    If xmlresult.ChildNodes(2).Text = "postal_code" Then
                        'long_nameを取得
                        strGeocode = xmlresult.ChildNodes(0).Text
                    End If
                End If
            Next
        
        '以下ステータスがOKでは無く問題があった場合
        Case "ZERO_RESULTS"
            strGeocode = "住所から緯度経度を出力出来ませんでした。"
        
        Case "OVER_QUERY_LIMIT"
            strGeocode = "クエリ数が割り当て量を超えています。"
        
        Case "REQUEST_DENIED"
            strGeocode = "リクエストが拒否されました。"
        
        Case "INVALID_REQUEST"
            strGeocode = "照会条件（address、components、latlngのいずれか）がありません。"
        
        Case "UNKNOWN_ERROR"
            strGeocode = "サーバーエラーでリクエストが処理できませんでした。"
    
    End Select
        
    
End_GeoCode:
    
    '結果を返す
    GeoCoding_zip = strGeocode
    
End With

Set HttpReq = Nothing
Set DomDoc = Nothing
    
End Function