Calculating distance between 2 zip codes

[crowegreg]

I import information pertaining to a shipment. I have to calculate the driving distance between 2 zip codes. Currently I go to mapquest, enter the two zip codes, then paste the distance within my DB. I've looked at applications that calculate the distance, but they do not calculate driving distance, which is what I need.



I'd like to calculate the distance when the data is being imported in . I've looked at Google Maps, and here is what I have now.
I've completed the beginning and ending of this process, I've created the http line and I've imported the xml data into my table

Here's what I need help with

This http request works:

http://maps.googleapis.com/maps/api/...l&sensor=false

How do I execute this http command within VBA, then save the results as an xml file?


Thank you in advance!!

[June7]

Never done anything like this and can't find much about VBA getting data from web page.

That link is an xml file. If you want to save it out as a text file Google: Access VBA save web page as text file.

Access 2010 offers a new webbrowser control.
Review http://office.microsoft.com/en-us/ac...101631434.aspx

Might be able to use it to open the link you show and then grab the text info from the page.
This link has VB.net code as example of that.
Hopefully could modify code to VBA syntax http://www.dreamincode.net/code/snippet5332.htm

[crowegreg]

Thanks for your info. I'll see what I can do with it, and update the thread.

[crowegreg]

Here's what I have at this time:

strhttp = "http://maps.googleapis.com/maps/api/...ix/xml?origins="
strhttp = strhttp & strOZip & "&destinations="
strhttp = strhttp & strDZip & "&units=imperial&sensor=false"


Application.FollowHyperlink (strhttp)

MsgBox "Determine the distance"

'import xml file
Application.ImportXML _
DataSource:="c:\scat\zipxml.xml", _
ImportOptions:=acStructureAndData

This works, but it requires keyboard entries. When the Google API is executed, a Microsoft Office window pops up, it is asking if you want to open the file, I select ok. A new IE window opens with xml data that I have requested. I have to manually save the file. After saving the file, I close that IE window. You'll see that I have a msgbox come up so it halts the execution of the VBA so I can get that file saved. I select ok to my message, and the process is completed.

So what I'm needing is:
How do I prevent or automatically answer the Microsoft Office message?
How do I automatically save the the XML file?
I can live with the msgbox being displayed, but if their is something better, I'm all for it.

Thanks in advance!!

[orange]

Double posting/Cross posting is frowned upon

You should at least advise the reader of the other post(s) so that volunteer time is not wasted.

If we don't know about the replicated posts, we may spend time solving/helping, that has already been resolved.

If readers are aware of other posts, they can follow various threads/responses, and possibly learn.

http://www.access-programmers.co.uk/...d.php?t=225339

Please read: http://www.excelguru.ca/content.php?184

[crowegreg]

I don't know how it got double posted.

[orange]

see post #5 at http://www.access-programmers.co.uk/...83#post1150683

Sample database (mdb) included.

[mbenton]

This post helped my through a project, thanks. I do have a question...I can use this distance calculator if I can change the speed to 45mph. Can you explain how this can be done? Thank you.