2017-04-06 07:06 阅读 170

XML解析特殊字符:Excel VBA运行时错误91

When XML parsing special characters - my specific issue is with ampersands - Excel VBA is throwing the error

Run time error 91: Object variable or With block variable not set

PHP code for XML Parsing:

$xml = '<?xml version="1.0" encoding="UTF-8" ?>
<root> <EngineerFName>'.$engineer_fname.'</EngineerFName>

Excel Vba code:

Dim XDoc1 As Object
Dim XHTML1 As IHTMLElement
Dim XURL1 As String
Dim CustomerName1 As String
Set XDoc1 = CreateObject("MSXML2.DOMDocument")
XDoc1.async = False: XDoc1.validateOnParse = False
XDoc1.Load ("" +  "?Id=" + Sheets(1).Range("D2"))
Set lists = XDoc1.DocumentElement
Set getFirstChild = lists.FirstChild
Set getCustomerName1 = lists.ChildNodes(1)
Set getEnglname = lists.ChildNodes(2)
Set getTopCustomer = lists.ChildNodes(3)

Sheets(1).Range("T5") = getCustomerName1.text
Sheets(1).Range("T6") = getFirstChild.text & Space(1) & getEnglname.text
Sheets(1).Range("T7") = getTopCustomer.text
Set XDoc1 = Nothing

Can anyone suggest a solution?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    duanchouyi6730 duanchouyi6730 2017-04-06 09:35

    There's a good article on your problem at Techrepublic - quoting the necessary part:

    When the XML parser finds an ampersand in the XML data, it expects to find a symbol name and a semicolon following it. The symbol name provides a symbolic reference to another entity or character such as the ampersand, greater-than, and less-than characters. The symbolic name for greater-than is gt and for less-than is lt. To include a greater-than character in the XML data, you must use the following syntax: &gt;

    If you already had the response from the PHP page then the solution to your problem is simply to do this:

    strXml = VBA.Replace(strXml, "&", "&amp;")

    But your issue is that you are using the Load method of the DOMDocument class and the PHP is emitting invalid XML. The PHP page should do this encoding for you - my guess is that it just queries some data store and slots it into an XML string an echos it without doing any validation on the values. Your Load method will not error, but the moment you want to parse the DOM, you get the problem.

    Given you are already referencing the MSXML library, perhaps your option is to use the XMLHTTP class to get the response, do the replacement yourself, and then load it the DOMDocument using the LoadXML method. See the code below - it is not tested as I don't know the parameter for your URL:

    Option Explicit
    Sub TextXMLGetAndParse()
        Dim strUrl As String
        Dim objXhr As MSXML2.XMLHTTP
        Dim strXml As String
        Dim objXmlDoc As MSXML2.DOMDocument
        Set objXhr = New MSXML2.XMLHTTP
        Set objXmlDoc = New MSXML2.DOMDocument
        ' do a XHR GET to your URL
        strUrl = "" + "?Id=" + Sheets(1).Range("D2")
        With objXhr
            .Open "GET", strUrl, False
            strXml = .responseXML
        End With
        ' do the clean-up that the PHP page should do for you
        strXml = VBA.Replace(strXml, "&", "&amp;")
        ' load that XML to you DOMDOcument
        objXmlDoc.LoadXML strXml
        ' check values
        Debug.Print objXmlDoc.DocumentElement.Text
        Debug.Print objXmlDoc.DocumentElement.FirstChild.Text
        Debug.Print objXmlDoc.DocumentElement.ChildNodes(1).Text
        Debug.Print objXmlDoc.DocumentElement.ChildNodes(2).Text
        Debug.Print objXmlDoc.DocumentElement.ChildNodes(3).Text
        ' clean up
        Set objXhr = Nothing
        Set objXhr = Nothing
    End Sub
    点赞 评论 复制链接分享