Userform to calculate Distances

[steve]

This may be far fetched....

I'm curious if it is possible to link Google Maps with an Excel Userform.
The user would enter the starting zip code, then the ending zip code. The
userform would return the calculated distance [in miles].

any thoughts?
Steve

 

[NickHK]

Steve,
Having a quick look at Google Maps and using the random zip codes 32024 &
32025, which in FL apparently, gives a URL of
http://maps.google.com/maps?sc=1&hl=en&output=html&saddr=32024&daddr=32025&btnG=Search

So you can send this URL either in a Web query or by HTTP and read the
result.

NickHK

 

[steve]

is it possible to contain all of these steps within vba? this webpage does
display the distance in a text box.

Steve,
Having a quick look at Google Maps and using the random zip codes 32024 &
32025, which in FL apparently, gives a URL of
http://maps.google.com/maps?sc=1&hl=en&output=html&saddr=32024&daddr=32025&btnG=Search

So you can send this URL either in a Web query or by HTTP and read the
result.

NickHK

This may be far fetched....

I'm curious if it is possible to link Google Maps with an Excel Userform.
The user would enter the starting zip code, then the ending zip code. The
userform would return the calculated distance [in miles].

any thoughts?
Steve

 

[NickHK]

Steve,
Did you try using that URL as the input for your Web query ?
See the results.

NickHK

is it possible to contain all of these steps within vba? this webpage
does
display the distance in a text box.

Steve,
Having a quick look at Google Maps and using the random zip codes 32024 &
32025, which in FL apparently, gives a URL of
http://maps.google.com/maps?sc=1&hl=en&output=html&saddr=32024&daddr=32025&btnG=Search

So you can send this URL either in a Web query or by HTTP and read the
result.

NickHK

This may be far fetched....

I'm curious if it is possible to link Google Maps with an Excel
Userform.
The user would enter the starting zip code, then the ending zip code.
The
userform would return the calculated distance [in miles].

any thoughts?
Steve

 

[steve]

Nick,

I've never used Web queries before...that's awesome! Below is the code I
have so far. I use the values from A1 and A2 of the active sheet, and put
them into the URL.
The distance in miles is my ultimate goal. here is the value I receive in
cell D14: "24.4 mi (about 36 mins)"
I'm guessing I just have to extract the 24.4? Is there anything else I
should know about web queries before implementing this into my program?


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://maps.google.com/maps?hl=en&output=html&f=d&saddr=" &
Worksheets("Sheet1").Range("A1").Value & "&daddr=" &
Worksheets("Sheet1").Range("A2").Value & "&btnG=Get+Directions" _
, Destination:=Range("C12"))

.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Steve,
Did you try using that URL as the input for your Web query ?
See the results.

NickHK

is it possible to contain all of these steps within vba? this webpage
does
display the distance in a text box.

Steve,
Having a quick look at Google Maps and using the random zip codes 32024 &
32025, which in FL apparently, gives a URL of
http://maps.google.com/maps?sc=1&hl=en&output=html&saddr=32024&daddr=32025&btnG=Search

So you can send this URL either in a Web query or by HTTP and read the
result.

NickHK

"steve" <[email protected]> ???gco?l¢Do¡Ps?D:[email protected]...

This may be far fetched....

I'm curious if it is possible to link Google Maps with an Excel
Userform.
The user would enter the starting zip code, then the ending zip code.
The
userform would return the calculated distance [in miles].

any thoughts?
Steve

 

[NickHK]

Steve,
You should check Google's output for invalid Zip codes, or when the distance
cannot be calculated for some reason.

Depending what you want to do with the results, yes strip out the distance,
maybe with
Dim Distance as single
Distance=csng(split(range("D14").Value," ")(0))

Also, once the web query is created, you do not need to recreate it each
time.
You can just call the .Refresh method when required ;
- From a button on the worksheet to do it manually
- From the Worksheet_Change event, filtering on the ranges A1 & A2

NickHK

Nick,

I've never used Web queries before...that's awesome! Below is the code I
have so far. I use the values from A1 and A2 of the active sheet, and put
them into the URL.
The distance in miles is my ultimate goal. here is the value I receive in
cell D14: "24.4 mi (about 36 mins)"
I'm guessing I just have to extract the 24.4? Is there anything else I
should know about web queries before implementing this into my program?


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://maps.google.com/maps?hl=en&output=html&f=d&saddr=" &
Worksheets("Sheet1").Range("A1").Value & "&daddr=" &
Worksheets("Sheet1").Range("A2").Value & "&btnG=Get+Directions" _
, Destination:=Range("C12"))

.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Steve,
Did you try using that URL as the input for your Web query ?
See the results.

NickHK

"steve" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...

is it possible to contain all of these steps within vba? this webpage
does
display the distance in a text box.

:

Steve,
Having a quick look at Google Maps and using the random zip codes 32024 &
32025, which in FL apparently, gives a URL of
http://maps.google.com/maps?sc=1&hl=en&output=html&saddr=32024&daddr=32025&btnG=Search

So you can send this URL either in a Web query or by HTTP and read the
result.

NickHK

"steve" <[email protected]> ???gco?l¢Do¡Ps?D:[email protected]...

This may be far fetched....

I'm curious if it is possible to link Google Maps with an Excel
Userform.
The user would enter the starting zip code, then the ending zip code.
The
userform would return the calculated distance [in miles].

any thoughts?
Steve