Userform to calculate Distances
 

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

Userform to calculate Distances
 

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=... 5&btnG=Search

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

NickHK

"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

Userform to calculate Distances
 

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

"NickHK" wrote:

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=... 5&btnG=Search

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

NickHK

"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

Userform to calculate Distances
 

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

NickHK

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

"NickHK" wrote:

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=... 5&btnG=Search

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

NickHK

"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

Userform to calculate Distances
 

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

"NickHK" wrote:

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

NickHK

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

"NickHK" wrote:

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=... 5&btnG=Search

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

NickHK

"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

Userform to calculate Distances
 

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

"steve" wrote in message
...
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

"NickHK" wrote:

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

NickHK

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

"NickHK" wrote:

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=... 5&btnG=Search

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

NickHK

"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