web query for driving directions

[steve]

From a userform, I want the user to enter a "Start" address, and an "End"
address. Then a command button runs a web query, which fills different
textboxes; one with the directions, one with total miles, and another with
total travel time.

I don't necessarily have a preference with what website is queried. I am
currently calculating the miles between two zip codes using the longitude and
latitudes, but I think a webquery with directions will be nicer.

thank you,
Steve

 

[Tim Williams]

Which part are you having a problem with?

Tim

 

[steve]

I'm using google maps, and I was having trouble determining which tables to
select. Here's the code I came up with, which seems to be working but I'm
not confident with it. Any advice you could offer would be great. For
example, a better way to check if the user entered an invalid address. Also,
how to deal with the workbook name that gets automatically assigned. How can
I re-use the same name, rather than add a new one each time?

ThisWorkbook.Worksheets("Report").Activate
ThisWorkbook.Worksheets("Report").Cells.Clear

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

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


'pull out the miles from "B1"
Dim Miles As Variant
Dim x As String

x = ThisWorkbook.Worksheets("Report").Range("B1")
Miles = Split(x, " ")
If x <> "" Then
frmWizard.tbTotalDistance = Miles(0)
Else
frmWizard.tbTotalDistance = "Invalid Zip Code"
End If