Hello,

I have a user who goes into the Internet using Bing map or Google map to get
sales person driving miles from point A to point B then input the mileage in
Excel 2007 expense report for reimbursement. The problem is there are many
salespersons and each individual sales person travels to many different
places. This is very time comsuming. What I like to is have the user input
in a cell(s) FROM - The address, City, State, Zip in another cell(s) the
TO - Address, City, State, Zip. and in another cell will display the
distance. I am justing wandering if there any add-in or a way to do this.
Any help, tips will be appreciated. Thank you in advance.

Regards

This macro uses google map to get the driving distance and time between
2 zipcodes. It should get you started. Let me know if you need more
help. I've done this with almost all map utilities including people in
England which uses Postal codes. I also can extract the GPS Longitude
and Latitude from the maps.



Sub FindDistance()

Const READYSTATE_COMPLETE = 4

URL = "http://maps.google.com/maps?hl=en&tab=wl"

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True


ie.Navigate URL

Do While ie.readystate < 4 Or ie.busy = True
DoEvents
Loop

Set StartLocation = ie.document.getelementbyid("d_d")

StartLocation.innertext = "07508"

Set EndLocation = ie.document.getelementbyid("d_daddr")
EndLocation.innertext = "10001"

Set submit = ie.document.getelementbyid("d_sub")
'submit.Select
submit.form.submit

Do While ie.readystate < 4 Or ie.busy = True
DoEvents
Loop

Call Dump(ie.document)

Set distance = ie.document.getelementbyid("dditd")
MsgBox (distance.innertext)


Set ie = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185400

Excel Live Chat

Thanks Joel I did some modification and woks great. Again I appreciate your
help.

Regards,


"joel" wrote in message
...

This macro uses google map to get the driving distance and time between
2 zipcodes. It should get you started. Let me know if you need more
help. I've done this with almost all map utilities including people in
England which uses Postal codes. I also can extract the GPS Longitude
and Latitude from the maps.



Sub FindDistance()

Const READYSTATE_COMPLETE = 4

URL = "http://maps.google.com/maps?hl=en&tab=wl"

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True


ie.Navigate URL

Do While ie.readystate < 4 Or ie.busy = True
DoEvents
Loop

Set StartLocation = ie.document.getelementbyid("d_d")

StartLocation.innertext = "07508"

Set EndLocation = ie.document.getelementbyid("d_daddr")
EndLocation.innertext = "10001"

Set submit = ie.document.getelementbyid("d_sub")
'submit.Select
submit.form.submit

Do While ie.readystate < 4 Or ie.busy = True
DoEvents
Loop

Call Dump(ie.document)

Set distance = ie.document.getelementbyid("dditd")
MsgBox (distance.innertext)


Set ie = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=185400

Excel Live Chat