CODEWITHSUNDEEP
phpxmlexcelxml-parsingvba
manoj
manoj

Reputation: 13

XML parsing special characters: Excel VBA Run time error 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>
<CustomerName>'.$customer_name.'</CustomerName>
<EngineerLName>'.$engineer_lname.'</EngineerLName>
<TopName>'.$dt_top_name.'</TopName>  
</root>';

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 ("http://www.beamon.com/windows_application/macro2.php" +  "?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?

Upvotes: 1

Views: 1834

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

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 = "http://www.beamon.com/windows_application/macro2.php" + "?Id=" + Sheets(1).Range("D2")
    With objXhr
        .Open "GET", strUrl, False
        .send
        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

Upvotes: 1

Related Questions