上次鐵人賽中，有提到使用Excel VBA來下載檔案，這次我們也是要做相關應用，不過這次會牽扯到網址列使用中文的問題，還有XML/HTML資料特殊字元的處理，所以今天先講解如何處理這部份的問題，筆者嘗試了很多的解決方案，以下為對繁體中文的實用者較好的選擇。

請將以下四個子程式複製到模組中使用：

Function HTMLToChr(strData As String) As String
    '轉換HTML文件的替換字串到不能使用的字元
    
    Dim ChrTable(24, 1) As String
    
    ChrTable(0, 0) = "&"
    ChrTable(0, 1) = "&"
    
    ChrTable(1, 0) = """"
    ChrTable(1, 1) = """
    
    ChrTable(2, 0) = "<"
    ChrTable(2, 1) = "<"
    
    ChrTable(3, 0) = ">"
    ChrTable(3, 1) = ">"
    
    ChrTable(4, 0) = "'"
    ChrTable(4, 1) = "&apos;"
    
    ChrTable(5, 0) = Chr(13)
    ChrTable(5, 1) = "
"
    
    ChrTable(6, 0) = Chr(10)
    ChrTable(6, 1) = "
"
        
    For i = 0 To 6
        strData = Replace(strData, ChrTable(i, 1), ChrTable(i, 0))
    Next i
    
    HTMLToChr = strData

End Function

Function ChrToHTML(strData As String) As String
    '轉換HTML文件不能使用的字元到替換字串
    
    Dim ChrTable(24, 1) As String
    
    ChrTable(0, 0) = "&"
    ChrTable(0, 1) = "&"
    
    ChrTable(1, 0) = """"
    ChrTable(1, 1) = """
    
    ChrTable(2, 0) = "<"
    ChrTable(2, 1) = "<"
    
    ChrTable(3, 0) = ">"
    ChrTable(3, 1) = ">"
    
    ChrTable(4, 0) = "'"
    ChrTable(4, 1) = "&apos;"
    
    ChrTable(5, 0) = Chr(13)
    ChrTable(5, 1) = "
"
    
    ChrTable(6, 0) = Chr(10)
    ChrTable(6, 1) = "
"
        
    For i = 0 To 6
        strData = Replace(strData, ChrTable(i, 0), ChrTable(i, 1))
    Next i
    
    ChrToHTML = strData

End Function
Public Function URLEncodeUTF8(str As String)
‘來源: http://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba
    Dim ScriptEngine As Object
    Dim encoded As String

    Set ScriptEngine = CreateObject("scriptcontrol")
    ScriptEngine.Language = "JScript"

    encoded = ScriptEngine.Run("encodeURIComponent", str)

    URLEncodeUTF8 = encoded
End Function

Public Function URLDecodeUTF8(str As String) As String
    Dim ScriptEngine As Object
    Dim encoded As String

    Set ScriptEngine = CreateObject("scriptcontrol")
    ScriptEngine.Language = "JScript"

    encoded = ScriptEngine.Run("decodeURIComponent", str)

    URLDecodeUTF8 = encoded
End Function

這四個子程式兩兩一組，分別是對XML/HTML特殊字元的轉換與換回，以及對網址列字串的編碼與解碼，關於XML/HTML特殊字元的解說，可參考Wikipedia：
XML與HTML字符實體引用列表

而網址列字串的編碼(URL encoding)的部份，可參考Wikipedia解說：
百分號編碼

以下程式複製到模組內，即可測試效果：

Sub XML_HTML特殊字元轉換與URL編碼測試()
    Dim strData As String
    Dim StrConv As String
    Dim StrConv2 As String
    
    Debug.Print "XML/HTML特殊字元轉換："
    strData = "&" & """" & "<>'" & Chr(13) & Chr(10)
    Debug.Print "原文：" & strData
    StrConv = ChrToHTML(strData)
    Debug.Print "轉換：" & StrConv
    StrConv2 = HTMLToChr(StrConv)
    Debug.Print "轉回：" & StrConv2
    
    Debug.Print "URL編碼："
    strData = "中文"
    Debug.Print "原文：" & strData
    StrConv = URLEncodeUTF8(strData)
    Debug.Print "轉換：" & StrConv
    StrConv2 = URLDecodeUTF8(StrConv)
    Debug.Print "轉回：" & StrConv2
    
End Sub

結果如下：

XML/HTML特殊字元轉換：
原文：&"<>'

轉換：&"<>&apos;

轉回：&"<>'

URL編碼：
原文：中文
轉換：%E4%B8%AD%E6%96%87
轉回：中文

這篇的程式將與後面要介紹的程式有關連，在這先介紹，以便後面幾天使用。