私は、住所を受け取り、Google マップ API を使用して緯度と経度を返す、すばらしい VBA スクリプトを見つけました。ただし、Google にはレート制限機能があり、これを多数の住所 (約 300 万) に対して実行したいと考えています。一方、スクリプトにはレート制限がないため、互換性がありません。前の要求が完了するまで次のセルに移動しないように、このスクリプトを編集することは可能ですか?
Function MyGeocode(address As String) As String
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
strAddress = URLEncode(address)
'Assemble the query string
strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
strQuery = strQuery & "address=" & strAddress
strQuery = strQuery & "&sensor=false"
'define XML and HTTP components
Dim googleResult As New MSXML2.DOMDocument60
Dim googleService As New MSXML2.XMLHTTP60
Dim oNodes As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
'create HTTP request to query URL - make sure to have
'that last "False" there for synchronous operation
googleService.Open "GET", strQuery, False
googleService.send
googleResult.LoadXML (googleService.responseText)
Set oNodes = googleResult.getElementsByTagName("geometry")
If oNodes.Length = 1 Then
For Each oNode In oNodes
strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
MyGeocode = strLatitude & "," & strLongitude
Next oNode
Else
MyGeocode = "Not Found (try again, you may have done too many too fast)"
End If
End Function
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
Dim StringLen As Long: StringLen = Len(StringVal)
If StringLen > 0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String
If SpaceAsPlus Then Space = "+" Else Space = "%20"
For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)
Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = "%0" & Hex(CharCode)
Case Else
result(i) = "%" & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, "")
End If
End Function
どのような助けでも大歓迎です、皆さんは最高です!
答え1
投稿したコードは、Excel に Google マップを照会する方法を指示するだけであり、実際に照会するわけではありません。関数を呼び出してMyGeocode
、照会するアドレスを Excel に指示する必要があります。
まずAlt+を押してF11Visual Basic Editor(VBE)にアクセスします。左側のパネルでワークブックの名前を右クリックし、入れる>モジュールこの新しいモジュールに VBA を貼り付けます。
このコードを実行するには、Microsoft XML、v6.0ライブラリ。VBEでクリックツール>参考文献下にスクロールして、ライブラリの横にあるボックスにチェックを入れます。[OK] をクリックします。
さて、Excel 自体に戻りましょう。
列 A:D に 300 万件のアドレスのサブセットがあるとします。
列 E ですべての住所フィールドを連結して、1 つのセルに住所全体が表示されるようにします。
=A2&" "&B2&" "&C2&" "&D2
次に、列 F で、次のように Google からの座標を呼び出すことができます。
=MyGeocode(E2)
呼び出しの合間に Excel に一時停止を指示する必要はありません。これは、Excel に実行を指示する回数=MyGeocode()
、つまり数式を Excel の何行下にコピーするかによって制御します。
安全のため、1日あたり2,500行以下で実行してください。Google の利用規約(セスさん、ありがとう)、ただし、1 日に 10 万回の通話に増やすためにいくらかのお金を費やすつもりがあるなら別ですが。
答え2
次のようにして、VBA でプログラムをたとえば 5 秒間停止させることができます。
Application.Wait(Now + TimeValue("0:00:05"))
あなたにとって役立つかもしれない
答え3
300 万のアドレスがある場合は、レート制限なしでサービスを利用できる商用アクセスの取得を検討してください。それ以外の場合は、次のようなものを使用してください。
Msgbox "Click to go on", , "Example"
から利用規約:
d. APIの制限
Google は、独自の裁量により、API の使用に制限を設け、適用します (たとえば、API リクエストの数やサービス提供可能なユーザー数の制限など)。ユーザーは、各 API に記載されている制限に同意し、これを回避しようとしないものとします。これらの制限を超えて API を使用する場合は、Google の明示的な同意を得る必要があります (Google は、そのような要求を拒否するか、追加の規約や使用料金への同意を条件として受け入れる場合があります)。そのような承認を求めるには、関連する Google API チームに連絡して情報を入手してください (たとえば、Google 開発者コンソールを使用するなど)。