After presenting a couple recent articles on mapping

Access - Google Maps Basics

This is the first in a series of articles on Mapping within Access using the Web Browser control. Today, I thought I’d demonstrate how easy it is to provide a map of an address to your users using Google Maps.

Access – Bing Maps Basics

After demonstrating how easy it was to use Google Maps to display a map of an Address or Geolocation coordinates in my article I wanted to demonstrate that the same was true for mapping with Microsoft Bing Maps.

in which we build up a URL and then needed to replace certain characters to make it compatible with Google/Bing… I thought I’d share a couple functions that can do this more efficiently and perform URL/URI encoding.

There are a number of ways that this can be approached and today I will present a variety:

• Using the Script Control Object
• Using the HTML File Object
• Using a Custom Function
• Using Excel’s Built-In Function
• Using Access’ Built-In Function

You’ll no doubt notice that both the script control and HTML file approaches work in extremely similar manners.

These only uses basic VBA code or built-in functions, uses no references and thus are bitness independent (so it will run equally on 32 or 64-bit installations).
 

Using the Script Control Object

Encoding

Public Function SC_encodeURI(ByVal sURI As String) As String
    On Error GoTo Error_Handler
    Dim oScriptControl        As Object

    Set oScriptControl = CreateObject("ScriptControl")
    With oScriptControl
        .Language = "JScript"
        SC_encodeURI = .Run("encodeURI", sURI)
    End With

Error_Handler_Exit:
    On Error Resume Next
    Set oScriptControl = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: encodeURI" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
End Function

Public Function SC_encodeURIComponent(ByVal sURI As String) As String
    'Encodes all characters, may be overkill!
    On Error GoTo Error_Handler
    Dim oScriptControl        As Object

    Set oScriptControl = CreateObject("ScriptControl")
    With oScriptControl
        .Language = "JScript"
        SC_encodeURIComponent = .Run("encodeURIComponent", sURI)
    End With

Error_Handler_Exit:
    On Error Resume Next
    Set oScriptControl = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: encodeURI" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
End Function

Using the HTML File Object

Encoding

Function HF_EncodeURI(ByVal sURI As String)
    HF_EncodeURI = CreateObject("HTMLFile").parentWindow.encodeURI(sURI)
End Function

Function HF_EncodeURIComponent(ByVal sURI As String)
    HF_EncodeURIComponent = CreateObject("HTMLFile").parentWindow.encodeURIComponent(sURI)
End Function

Using a Custom Function

Encoding

' sLang : HTML or PHP
' ? encodeURL("https://www.bing.com/maps/?v=2&where1=Mount Rushmore National Memorial, 13000 SD Highway 244, Keystone, SD, United States", "html")
'       https%3A%2F%2Fwww.bing.com%2Fmaps%2F%3Fv%3D2%26where1%3DMount+Rushmore+National+Memorial%2C+13000+SD+Highway+244%2C+Keystone%2C+SD%2C+United+States
' ? "https://www.bing.com/maps/?v=2&where1=" & encodeURL("Mount Rushmore National Memorial, 13000 SD Highway 244, Keystone, SD, United States", "PHP")
'       https://www.bing.com/maps/?v=2&where1=Mount+Rushmore+National+Memorial%2C+13000+SD+Highway+244%2C+Keystone%2C+SD%2C+United+States
Function encodeURL(ByVal sURL As String, ByVal sLang As String) As String
    Dim aChar()               As Variant   'Plain text characters
    Dim aCharHex()            As Variant   'Characters' encoded Hex Value
    Dim i                     As Long

    aChar = Array(" ", "!", """", "#", "$", "%", "&", "'", "(", ")", "*", "+", ",", "-", ".", "/", ":", _
                  ";", "<", "=", ">", "?", "@", "[", "\", "]", "^", "_", "`", "{", "|", "}", "~")
    If sLang = "HTML" Then
        aCharHex = Array("%20", "%21%20", "%22", "%23", "%24", "%25", "%26", "%27", "%28", "%29", "%2A", "%2B", _
                         "%2C", "-", ".", "%2F", "%3A", "%3B", "%3C", "%3D", "%3E", "%3F", "%40", "%5B", "%5C", _
                         "%5D", "%5E", "_", "%60", "%7B", "%7C", "%7D", "%7E")
    Else
        'https://www.php.net/urlencode
        aCharHex = Array("+", "%21%20", "%22", "%23", "%24", "%25", "%26", "%27", "%28", "%29", "%2A", "%2B", _
                         "%2C", "-", ".", "%2F", "%3A", "%3B", "%3C", "%3D", "%3E", "%3F", "%40", "%5B", "%5C", _
                         "%5D", "%5E", "_", "%60", "%7B", "%7C", "%7D", "%7E")
    End If

    For i = 0 To UBound(aChar)
        sURL = Replace(sURL, aChar(i), aCharHex(i))
    Next i

    encodeURL = sURL
End Function

The beauty here is you can customize the arrays as required by your specific situation, but I have built the current version using the most common characters.

This function behaves in a similar manner to the EncodeURIComponent function.
 

Using Excel’s Built-In Function

If you are working with Excel, I wanted to point out that Excel processes the EncodeURL function which act in a similar manner to both of the encodeURIComponent functions found above.
 

Using Access’ Built-In Function

I found that similar to Excel, Access possesses a HTMLEncode function. I tried it out, and must be misunderstanding it’s ‘raison d’être’ or misusing it, but I couldn’t get it to do anything worthwhile. That said, I thought I’d still briefly mention it in case it is a bug and/or you have more luck than I do.
 

Difference between EncodeURI & EncodeURIComponent

If you check the documentation you’ll find that

Let’s simply look at a concrete example as a picture is worth a thousand words:

Say we have a URL like:

https://www.bing.com/maps/?v=2&where1=Mount Rushmore National Memorial,13000 SD Highway 244, Keystone, SD, United States

EncodeURI will give:

https://www.bing.com/maps/?v=2&where1=Mount%20Rushmore%20National%20Memorial,13000%20SD%20Highway%20244,%20Keystone,%20SD,%20United%20States

and EncodeURIComponent will return:

https%3A%2F%2Fwww.bing.com%2Fmaps%2F%3Fv%3D2%26where1%3DMount%20Rushmore%20National%20Memorial%2C13000%20SD%20Highway%20244%2C%20Keystone%2C%20SD%2C%20United%20States

Notice the difference! The EncodeURIComponent encodes everything, not just the Query component. So be aware of the difference and choose accordingly.
 

Other Resources on the Subject

encodeURI() - JavaScript | MDN

The encodeURI() function encodes a URI by replacing each instance of certain characters by one, two, three, or four escape sequences representing the UTF-8 encoding of the character (will only be four escape sequences for characters composed of two surrogate characters). Compared to encodeURICompone…

encodeURIComponent() - JavaScript | MDN

The encodeURIComponent() function encodes a URI by replacing each instance of certain characters by one, two, three, or four escape sequences representing the UTF-8 encoding of the character (will only be four escape sequences for characters composed of two surrogate characters). Compared to encodeU…

WorksheetFunction.EncodeUrl method (Excel)

URL encodes the argument.

Application.HtmlEncode method (Access)

Converts a string to an HTML-encoded string.