Retrieve data from google maps!

[Scorpion Steve]

Afternoon virtual friends!!
Can anyone please enlighten me as to what tags I can use to search for information on how to create my next project please?
Explanation:
GOAL:
To enter a postal code/ZIP in my excel spreadsheet, then activate a macro to take that postcode along with some other data of mine and paste it into google maps or such like feature/site. I would like to return certain data.

Now, I am aware of vba's and external data but I am struggling on making the macro copy a cell's data and placing it into a site's search box etc etc.

Any hints please?
I would like to figure this out myself but more than happy for help/tips! Many thanks!

 

[bertie]

Place your post code in cell A1 on Sheet1 and run the test Procedure.

Place the code below in the ThisWorkboook module. This works using Excel 2010.

The highlighted code gives the id of the text box and search button on the Google maps site.

==================
Edit:
You can find these "ids" using Google chrome. Right click the object and select "inspect Element".
============

[color=darkblue]Sub[/color] test()
   [color=darkblue]Dim[/color] sPostCode [color=darkblue]As[/color] [color=darkblue]String[/color]
   
   sPostCode = Sheets("Sheet1").Range("A1").Value
   LaunchGoogleMaps sPostCode
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Private[/color] [color=darkblue]Sub[/color] LaunchGoogleMaps([color=darkblue]ByVal[/color] sPostCode [color=darkblue]As[/color] [color=darkblue]String[/color])
    [color=darkblue]Const[/color] strURL_c [color=darkblue]As[/color] [color=darkblue]String[/color] = "http://maps.google.co.uk/"
    
    [color=darkblue]Dim[/color] objIE [color=darkblue]As[/color] [color=darkblue]Object[/color]
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] Err_Hnd
    
    [color=darkblue]Set[/color] objIE = CreateObject("InternetExplorer.Application")
    
    [color=darkblue]With[/color] objIE
      .Navigate strURL_c
      [color=darkblue]Do[/color] [color=darkblue]While[/color] .Busy: DoEvents: [color=darkblue]Loop[/color]
      [color=darkblue]Do[/color] [color=darkblue]While[/color] .ReadyState <> 4: DoEvents: [color=darkblue]Loop[/color]
      .Visible = [color=darkblue]True[/color]
      
      .Document.getElementById("[COLOR="Red"]q_d[/COLOR]").Value = sPostCode
      .Document.getElementById("[COLOR="red"]btnG[/COLOR]").Click
    [color=darkblue]End[/color] [color=darkblue]With[/color]

Err_Hnd: [color=green]'(Fail gracefully)[/color]
    objIE.Visible = [color=darkblue]True[/color]
    [color=darkblue]Set[/color] objIE = [color=darkblue]Nothing[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Scorpion Steve]

woww! I'll give it a try! amazing help! Thanking you very much!
be right back!

 

[Scorpion Steve]

that's good, where you have placed in the "TEST POST CODE", how can I have that saying: take cell A1's data and search that?
The other thing is needing to know how to take the data from google! I'm trying to copy and paste during a macro recording! I'm needing the mileage between 2 postcodes.
Its ok that I'm not asking you to do my work for me. A helpful guide towards what to look for would be appreciated just as much!
Steve

 

[bertie]

Unfortunately, I can't isolate the id or name of the html element which displays the distance.

This is as far as I have got. I have tested for between two towns.



Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 97px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Coatbridge</TD><TD>Airdrie</TD></TR></TBODY></TABLE>


Again I have highlighted the id of the elements we are targetting.

[COLOR=darkblue]Sub[/COLOR] test()
   [COLOR=darkblue]Dim[/COLOR] area1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] area2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] distance [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
 
   area1 = Sheets("Sheet1").Range("A1").Value
   area2 = Sheets("Sheet1").Range("B1").Value
 
   LaunchGoogleMaps area1, area2, distance
 
   [COLOR=green]'MsgBox "Distance between " & area1 & " and " & area2 & " is: " & distance[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] LaunchGoogleMaps([COLOR=darkblue]ByVal[/COLOR] area1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], _
                             [COLOR=darkblue]ByVal[/COLOR] area2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], _
                             [COLOR=darkblue]ByRef[/COLOR] distance [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR])
 
    [COLOR=darkblue]Const[/COLOR] strURL_c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "[URL]http://maps.google.co.uk/[/URL]"
    [COLOR=darkblue]Dim[/COLOR] objIE [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
 
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] Err_Hnd
 
    [COLOR=darkblue]Set[/COLOR] objIE = CreateObject("InternetExplorer.Application")
 
    [COLOR=darkblue]With[/COLOR] objIE
      .Navigate strURL_c
      [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] .Busy: DoEvents: [COLOR=darkblue]Loop[/COLOR]
      [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] .ReadyState <> 4: DoEvents: [COLOR=darkblue]Loop[/COLOR]
      .Visible = [COLOR=darkblue]True[/COLOR]
 
      .Document.getElementById("[COLOR=red]d_d").[/COLOR]Value = [COLOR=red]area1[/COLOR]
      .Document.getElementById("[COLOR=red]d_daddr[/COLOR]").Value = [COLOR=red]area2[/COLOR]
      .Document.getElementById("[COLOR=red]d_sub[/COLOR]").Click [COLOR=seagreen]'button[/COLOR]
 
      [COLOR=green]'distance = .Document.getElementById("altroute_0").Value[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
Err_Hnd: [COLOR=green]'(Fail gracefully)[/COLOR]
    objIE.Visible = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]Set[/COLOR] objIE = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[sirguise]

I've been tinkering with some of this as well. I built the code in my SQL DB to build the URL and then drop into excel (for example: http://maps.googleapis.com/maps/api...501+SW+1st+Ave+Portland+OR+97201&sensor=false), in excel I return say 2,000 of these at a time.

I open web query and pass my first url, this returns XML data that I can drop onto a second page. On that page, I create a search for the latitude and longitude values. I pull those values and import back into my SQL db, "rinse and repeat" for each URL.

I lastly update the SQL DB with this formula: =ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*3960 (this is the excel version), which calcs mileage between two points.

Of course these steps are all wrapped into a nice little VBA script so it can run unmonitored. And, you can certainly do all these steps within excel rather than pushing back and forth to a SQL DB.

Hope that helps put you on the path!

Dave

 

[Scorpion Steve]

Nice! I really want to complete a course on excel but I'm scared of how much there is lol
Cheers