[Excel VBA] Two Ways to Decode URL-Encoded Strings

Japanese characters and symbols in URLs are converted into percent-encoding (e.g., %E3%81%AA). In Excel VBA, you can easily revert (decode) these strings to their original form using JScript or PowerShell.

This article summarizes code examples and key points for both methods.

Operating Environment

Method 1: Calling decodeURI from ScriptControl (JScript)

Using ScriptControl allows you to call JavaScript functions directly from VBA. No external references are required, and it works in both 32-bit and 64-bit environments.

Sub DecodeUrlWithJScript()
    
    Dim encodedText As String
    Dim sc          As Object      ' MSScriptControl.ScriptControl
    
    encodedText = "E6%A3%AE%20Rintaro"
    
    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"
    
    ' Decode using decodeURI
    encodedText = sc.CodeObject.decodeURI(encodedText)
    
    MsgBox encodedText, vbInformation, "Decode Result"
    
End Sub

Key Points

• Language = "JScript" sets up the JavaScript execution environment.
• decodeURI interprets the encoded string as UTF-8 and restores the original characters.
• ScriptControl is a standard Windows COM component, so no additional installation is needed.

Method 2: Calling System.Web.HttpUtility.UrlDecode via PowerShell

This method uses the URL decode function of the .NET Framework via PowerShell. It executes asynchronously and captures standard output on the VBA side.

Sub DecodeUrlWithPowerShell()
    
    Dim encodedText As String
    Dim psCmd       As String
    Dim wsh         As Object      ' WScript.Shell
    Dim execObj     As Object      ' WshScriptExec
    
    encodedText = "E6%A3%AE%20Rintaro"
    
    ' Construct PowerShell command
    psCmd = "powershell -NoProfile -Command " & _
            """Add-Type -AssemblyName System.Web;" & _
            "Write-Output ([System.Web.HttpUtility]::UrlDecode('" & encodedText & "'))"""
    
    Set wsh = CreateObject("WScript.Shell")
    Set execObj = wsh.Exec(psCmd)
    
    MsgBox execObj.StdOut.ReadAll, vbInformation, "Decode Result"
    
End Sub

Key Points

• -NoProfile skips loading the PowerShell profile, speeding up execution.
• Add-Type loads the System.Web assembly to use the UrlDecode method.
• The Exec method runs asynchronously, but StdOut.ReadAll waits for completion while retrieving the result.

How to Choose

Summary

• ScriptControl is lightweight and easy to implement.
• PowerShell can leverage the rich libraries of .NET and is flexible for complex processing.

Both methods can be used without additional libraries, so please choose the one that suits your needs. Automating URL decoding will make integration with external APIs and web forms much smoother.