Zip Code reference and ZOHO sheets

[HomePro]

So i was able to build the Zip Code look up from a spreadsheet I found online. It works fine in Excel. I uploaded it into ZOHo Sheets [ its what our office uses] and although they say it supports VBA it will not run.

Here is the code:

Sub putdistanceonsheet()
Dim wks1 As Worksheet
Dim blnDone As Boolean, strBaseAddr As String, strGuestAddr As String
Dim strDist As String, p As Integer
Dim strAPIKey As String, strTravelMode As String, strRegion As String
Set wks1 = Application.ActiveSheet
blnDone = False
strAPIKey = "CnjvtWyrEGlWezTAI0zas2aNeCpJWsEVVOnulmXFwcV6E9oHrjCoE7XIj299YBCn "
'strTravelMode = Range("C3")
'strRegion = Range("C4")
wks1.Activate
wks1.Range("C5").Select
strBaseAddr = Range("A1")
strBaseAddr = WorksheetFunction.EncodeURL(strBaseAddr)
While Not blnDone
'select up to 20 destination addresses for 1 query
strDestAddr = ""
For r = 0 To 40
    If Not IsEmpty(ActiveCell.Offset(r, 0)) Then
        If Len(strDestAddr) > 0 Then
            strDestAddr = strDestAddr & "|"
        End If
        strDestAddr = strDestAddr & ActiveCell.Offset(r, 0)
    Else
        Exit For
    End If
Next
strDestAddr = WorksheetFunction.EncodeURL(strDestAddr)
aryDistData = gDist(strBaseAddr, strDestAddr, strAPIKey, strTravelMode, strRegion)
If aryDistData(0, 0) <> "NO DATA" Then
    For d = 0 To UBound(aryDistData)
        ActiveCell.Offset(d, 2) = aryDistData(d, 0)
        ActiveCell.Offset(d, 1) = aryDistData(d, 1)
    Next
    ActiveCell.Offset(r, 0).Select 'move down to the next row
End If
If IsEmpty(ActiveCell) Then
    blnDone = True
End If
Wend
'sORT AND PREP SHEET
    Range("A5:E40").Select
    Range("E5").Activate
    ActiveWindow.SmallScroll Down:=-160
    ActiveWorkbook.Worksheets("Inspector Travel Times").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Inspector Travel Times").Sort.SortFields.Add2 Key _
        :=Range("E5:E20"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Inspector Travel Times").Sort
        .SetRange Range("A5:E20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1:B2").Select
End Sub

Function gDist(strOrig, strDest, strAPI, strMode, strReg)

Dim strURL As String
Dim objHttp As MSXML2.XMLHTTP60
Dim objDom As DOMDocument60
Dim aryDest() As String
' in case region code is not included from cheet
If Len(strReg) > 0 Then
    strRegURL = "&region=" & strReg
End If
        strURL = "[URL]https://maps.googleapis.com/maps/api/distancematrix/xml?units=imperial[/URL]" & _
        "&origins=" & strOrig & _
        "&destinations=" & strDest & _
        "&traffic_mode1=optimistic" & _
        "&mode=" & strMode & _
        strRegURL & _
        "&key=" & strAPI
Set objHttp = New MSXML2.XMLHTTP60
With objHttp
    .Open "GET", strURL, False
    .setRequestHeader "Content-Type", "application/x-www-form-URLEncoded"
    .Send
End With
Set objDom = New DOMDocument60
objDom.LoadXML (objHttp.responseText)
'objDom.LoadXML objXHTTP.responseText
Dim strStatus As String
strStatus = objDom.SelectSingleNode("//status").Text
If strStatus = "OK" Then 'we have data to parse
    numrows = objDom.SelectNodes("//row/element").Length
    ReDim aryDest(numrows - 1, 1)
    'get the rows
    For x = 1 To numrows
        Dim datanode As MSXML2.IXMLDOMNode
        Set datanode = objDom.SelectNodes("//row/element")(x - 1)
        If datanode.SelectNodes("status")(0).Text = "OK" Then
            strDur = datanode.ChildNodes(1).ChildNodes(0).Text
            strDur = Str(Round(Val(strDur) / 60, 1))  'convert seconds to minutes
            strDist = datanode.ChildNodes(2).ChildNodes(0).Text
            strDist = Round(Val(strDist) / 1609.344, 3) 'convert from meters to miles
            aryDest(x - 1, 0) = strDur  'durations in seconds, converted to minutes
            aryDest(x - 1, 1) = strDist  'distance in meters converted to miles
        Else
            aryDest(x - 1, 0) = datanode.SelectNodes("status")(0).Text
            aryDest(x - 1, 1) = datanode.SelectNodes("status")(0).Text
        End If
    Next
Else
    ReDim aryDest(0, 0)
    aryDest(0, 0) = "NO DATA"
End If
Set objDom = Nothing
Set objHttp = Nothing
gDist = aryDest
End Function

Inspector Travel Times.xlsm
	A	B	C	D	E
1	32081
2
3			To Location	Mile to Destination	Travel Time (minutes)
4
5	Austin	Barbour Jr.	32081	0	0
6	Gary	Burbage	32256	19	26
7	Larry	Newland	32224	21	26
8	Joe	Galison	32259	16	27
9	Anthony	Masciello	32259	16	27
10	Bryan	VanArsdale	32257	21	28
11	Shannon	Trahan	32216	23	30
12	Jeff	Smith	32207	26	32
13	Cristina	Santiago	32211	29	36
14	Ford	Mullen	32205	33	38
15	Marvin	Pittman	32209	34	38
16	Andy	Toole	32233	31	42
17	Ryan	Coker	32011	53	59
18	Thomas	DeGrove	32034	58	69
19	Brad	Rushing	32034	58	69
Inspector Travel Times

 

[Joe4]

You would probably be better off posting this question to a forum that specializes in Zoho Sheets, like this one: https://help.zoho.com/portal/en/community/zoho-sheet

 

[HomePro]

Appreciated. That will help

 

[Joe4]

You are welcome.

 

[HomePro]

Appreciated. That will help

Does not seem that that board wil be very helpful but I will wait and hope. In the meantime does anyone with more expirence than me think they can consolidate the script into one macro as opposed to three section? perhaps then it will work for me in Zoho.