Does VBA have any built in URL decoding?

Question

I just need to decode a URL, for example, replace %2E with . I can hack out a method if one isn't build in, but my assumption is that there must be a URL decoding tool already existing.

possible duplicate of [How can I URL encode a string in Excel VBA?](http://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba) — Jamie Bull, Jun 19 '14 at 07:53
You can use windows native JScript [`encodeURIComponent()` via `htmlfile` ActiveX](http://stackoverflow.com/a/34601029/2165759). — omegastripes, Jun 18 '16 at 07:45

score 9 · Answer 1 · edited Jul 19 '11 at 06:39

9

Here's a snippet I wrote years ago

-markus

Public Function URLDecode(sEncodedURL As String) As String

On Error GoTo Catch

Dim iLoop   As Integer
Dim sRtn    As String
Dim sTmp    As String

If Len(sEncodedURL) > 0 Then
    ' Loop through each char
    For iLoop = 1 To Len(sEncodedURL)
        sTmp = Mid(sEncodedURL, iLoop, 1)
        sTmp = Replace(sTmp, "+", " ")
        ' If char is % then get next two chars
        ' and convert from HEX to decimal
        If sTmp = "%" and LEN(sEncodedURL) + 1 > iLoop + 2 Then
            sTmp = Mid(sEncodedURL, iLoop + 1, 2)
            sTmp = Chr(CDec("&H" & sTmp))
            ' Increment loop by 2
            iLoop = iLoop + 2
        End If
        sRtn = sRtn & sTmp
    Next
    URLDecode = sRtn
End If

Finally:
    Exit Function
Catch:
    URLDecode = ""
    Resume Finally
End Function

edited Jul 19 '11 at 06:39

Cheran Shunmugavel

8,319
1
33
40

answered Jul 18 '11 at 14:52

markus diersbock

99
1
1

In MAC version of Excel 2011 the functin CDec does not exist. I replaced it with CDbl with success – qtips May 18 '15 at 16:35
This doesn't work outside the US. – Gustav Apr 29 '22 at 13:11
Worked, though it fails to decode UTF-8 characters. Also I had to make the argument to `ByVal sEncodedURL As String` (added ByVal) – MarcinWolny Aug 05 '22 at 14:23

score 5 · Accepted Answer · edited May 23 '17 at 12:24

5

No.

But here's one: URL Encoder and Decoder for VB

Or something along the lines of (possibly not complete):

Public Function URLDecode(ByVal strEncodedURL As String) As String
   Dim str As String
   str = strEncodedURL 
   If Len(str) > 0 Then
      str = Replace(str, "&amp", " & ")
      str = Replace(str, "&#03", Chr(39))
      str = Replace(str, "&quo", Chr(34))
      str = Replace(str, "+", " ")
      str = Replace(str, "%2A", "*")
      str = Replace(str, "%40", "@")
      str = Replace(str, "%2D", "-")
      str = Replace(str, "%5F", "_")
      str = Replace(str, "%2B", "+")
      str = Replace(str, "%2E", ".")
      str = Replace(str, "%2F", "/")

      URLDecode = str
  End If

End Function

Also, take a look at How can I URL encode a string in Excel VBA?

edited May 23 '17 at 12:24

Community

1
1

answered Feb 15 '11 at 00:08

Mitch Wheat

295,962
43
465
541

5

If to suppose that there is only USA in the world, the list is complete. – Paul Oct 23 '15 at 16:38
@paul: see this bit?: "...(possibly not complete)" – Mitch Wheat Oct 23 '15 at 22:51
Thanks for the idea Mitch... @Paul, you are right, per say.. "%20" (space) is missing.. – PravyNandas Mar 27 '19 at 05:56

score 5 · Answer 3 · answered Jun 12 '18 at 09:26

EncodeURL and DecodeURL function using htmlfile object(Late binding)

I got this source from this site: http://cocosoft.kr/442

Function ENCODEURL(varText As Variant, Optional blnEncode = True)
    Static objHtmlfile As Object
    If objHtmlfile Is Nothing Then
        Set objHtmlfile = CreateObject("htmlfile")
        With objHtmlfile.parentWindow
            .execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
        End With
    End If
    If blnEncode Then
        ENCODEURL = objHtmlfile.parentWindow.encode(varText)
    End If
End Function

Function DECODEURL(varText As Variant, Optional blnEncode = True)
    Static objHtmlfile As Object
    If objHtmlfile Is Nothing Then
        Set objHtmlfile = CreateObject("htmlfile")
        With objHtmlfile.parentWindow
            .execScript "function decode(s) {return decodeURIComponent(s)}", "jscript"
        End With
    End If
    If blnEncode Then
        DECODEURL = objHtmlfile.parentWindow.decode(varText)
    End If
End Function

For example,

str = ENCODEURL("/?&=") 'returns "%2F%3F%26%3D"
str = DECODEURL("%2F%3F%26%3D") 'returns "/?&="

score 1 · Answer 4 · answered Feb 05 '20 at 23:39

Here is the code from the URL posted in another answer in case it goes down as it works great.

http://www.freevbcode.com/ShowCode.asp?ID=1512

Public Function URLEncode(StringToEncode As String, Optional _
   UsePlusRatherThanHexForSpace As Boolean = False) As String

Dim TempAns As String
Dim CurChr As Integer
CurChr = 1
Do Until CurChr - 1 = Len(StringToEncode)
  Select Case Asc(Mid(StringToEncode, CurChr, 1))
    Case 48 To 57, 65 To 90, 97 To 122
      TempAns = TempAns & Mid(StringToEncode, CurChr, 1)
    Case 32
      If UsePlusRatherThanHexForSpace = True Then
        TempAns = TempAns & "+"
      Else
        TempAns = TempAns & "%" & Hex(32)
      End If
   Case Else
         TempAns = TempAns & "%" & _
              Format(Hex(Asc(Mid(StringToEncode, _
              CurChr, 1))), "00")
End Select

  CurChr = CurChr + 1
Loop

URLEncode = TempAns
End Function


Public Function URLDecode(StringToDecode As String) As String

Dim TempAns As String
Dim CurChr As Integer

CurChr = 1

Do Until CurChr - 1 = Len(StringToDecode)
  Select Case Mid(StringToDecode, CurChr, 1)
    Case "+"
      TempAns = TempAns & " "
    Case "%"
      TempAns = TempAns & Chr(Val("&h" & _
         Mid(StringToDecode, CurChr + 1, 2)))
       CurChr = CurChr + 2
    Case Else
      TempAns = TempAns & Mid(StringToDecode, CurChr, 1)
  End Select

CurChr = CurChr + 1
Loop

URLDecode = TempAns
End Function


' URLDecode function in Perl for reference
' both VB and Perl versions must return same
'
' sub urldecode{
'  local($val)=@_;
'  $val=~s/\+/ /g;
'  $val=~s/%([0-9A-H]{2})/pack('C',hex($1))/ge;
'  return $val;
' }

Does VBA have any built in URL decoding?

4 Answers4

Linked