How to Google the street address and return the value back into Excel using Excel VBA? -

i have list of on 50,000 places in excel (eg. arches national park, cornell museum of glass). when type "cornell museum of glass" google, returns address of "1 museum way, corning, ny 14830". need full street address street #, street name, , postal code. have state ny though(in excel). know how search address on google(typing cornell museum of glass), return street address onto excel(1 museum way, corning, ny 14830)? know vba capable of doing this, , don't want manually 50,000 times, plus i'm total newbie @ vba. if knows how appreciate it! thank in advance.

the following should information needed solve problem

here post shows how use vba , xml interact google maps (via google maps api).
, here google's place api, you're looking (requesting/receiving information google).

answer
have created working test. have create google api key, can go here that. once have api key, put code , run it.

note google restrict how api key can used each day. free user seems 1000 "requests" "place" api each day. annoyingly, calls seem worth more "requests" others. code makes 2 calls google's "place" api, google counts them 11 requests. because call "place/textsearch" worth 10 "requests". other call places/details worth 1. means you're limited 90 entries each day/free api key method.

here google's place/search documentation
here google's place/details documentation

finally, this terrific post topic, based code off it.

test output:

code:

sub mytest()     dim xhrrequest xmlhttp60     dim domdoc domdocument60     dim domdoc2 domdocument60     dim placeid string     dim query string     dim nodes ixmldomnodelist     dim node ixmldomnode      'you have replace spaces +     query = "cornell+museum+of+glass"      'you must acquire google api key , enter here     dim googlekey string     googlekey = "imaginary api key" 'your api key here      'send "get" request place/textsearch     set xhrrequest = new xmlhttp60      xhrrequest.open "get", "https://maps.googleapis.com/maps/api/place/textsearch/xml?" & _         "query=cornell+museum+of+glass&key=" & googlekey, false     xhrrequest.send      'save response document     set domdoc = new domdocument60     domdoc.loadxml xhrrequest.responsetext      'find first node called "place_id" , child of "result" node     placeid = domdoc.selectsinglenode("//result/place_id").text      'recycling objects (could use new ones)     set domdoc = nothing     set xhrrequest = nothing      'send "get" request place/details     set xhrrequest = new xmlhttp60     xhrrequest.open "get", "https://maps.googleapis.com/maps/api/place/details/xml?placeid=" & placeid & _     "&key=" & googlekey, false     xhrrequest.send      'save response document     set domdoc = new domdocument60     domdoc.loadxml xhrrequest.responsetext      dim output string     dim s string      'hacky way postal code, might want rewrite after learning more     set nodes = domdoc.selectnodes("//result/address_component/type")     each node in nodes         s = node.text         if s = "postal_code"             'this bad, should search "long_name", did here assume "long_name first child"             output = vbnewline & "postal code: " & node.parentnode.firstchild.text         end if     next node      'output     msgbox "formatted address: " & domdoc.selectsinglenode("//result/formatted_address").text & output end sub 

'####

edit: using place api rather geocoding api