Prev: Chart Problem
Next: Excel 2007 Object Model Map
From: CAM on 7 Mar 2010 14:29
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

From: joel on 7 Mar 2010 15:17

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/showthread.php?t=185400

[url=&quot;http://www.thecodecage.com/forumz/&quot;]Excel Live Chat[/url]

From: CAM on 7 Mar 2010 20:59
Thanks Joel I did some modification and woks great. Again I appreciate your
help.

Regards,


"joel" <joel.47h04i(a)thecodecage.com> wrote in message
news:joel.47h04i(a)thecodecage.com...
>
> 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/showthread.php?t=185400
>
> [url=&quot;http://www.thecodecage.com/forumz/&quot;]Excel Live Chat[/url]
>

 | 
Pages: 1
Prev: Chart Problem
Next: Excel 2007 Object Model Map