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.

Osmund

I don't think there's anything built in. – Pasadis

possible duplicate of How can I URL encode a string in Excel VBA? – Forland

You can use windows native JScript encodeURIComponent() via htmlfile ActiveX. – Tourane

Answer 1

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?

Eladiaelaeoptene

If to suppose that there is only USA in the world, the list is complete. – Moriarty

@paul: see this bit?: "...(possibly not complete)" – Eladiaelaeoptene

Thanks for the idea Mitch... @Paul, you are right, per say.. "%20" (space) is missing.. – Recall

Answer 2

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

Seawards

In MAC version of Excel 2011 the functin CDec does not exist. I replaced it with CDbl with success – Arjuna

This doesn't work outside the US. – Porter

Worked, though it fails to decode UTF-8 characters. Also I had to make the argument to ByVal sEncodedURL As String (added ByVal) – Colston

Answer 3

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 "/?&="

Matteroffact

If the text is empty the result is undefined, which is not good, you can change the code accordingly. This one is better than the upper one as it keeps the unicode characters like ó etc... – Heathenish

Answer 4

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;
' }

Microorganism