VBA XML parsing ''Object variable or With block reference not set'

Question

I'm wanting to use VBA in MS Access to read in some XML, but I'm getting a 'Object variable or With block reference not set' error when parsing this XML...

    <?xml version="1.0"?>
<GetCompetitivePricingForASINResponse xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01">
    <GetCompetitivePricingForASINResult ASIN="B002L7HJAA" status="Success">
        <Product
            xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01"
            xmlns:ns2="http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd">
            <Identifiers>
                <MarketplaceASIN>
                    <MarketplaceId>A1F83G8C2ARO7P</MarketplaceId>
                    <ASIN>B002L7HJAA</ASIN>
                </MarketplaceASIN>
            </Identifiers>
            <CompetitivePricing>
                <CompetitivePrices>
                    <CompetitivePrice belongsToRequester="false" condition="New" subcondition="New">
                        <CompetitivePriceId>1</CompetitivePriceId>
                        <Price>
                            <LandedPrice>
                                <CurrencyCode>GBP</CurrencyCode>
                                <Amount>14.45</Amount>
                            </LandedPrice>
                        </Price>
                    </CompetitivePrice>
                </CompetitivePrices>
            </CompetitivePricing>
        </Product>
    </GetCompetitivePricingForASINResult>
</GetCompetitivePricingForASINResponse>

using this code...

Public Function READXML()

Dim objXMLNode1 As MSXML2.IXMLDOMNodeList

Set objXMLDoc = New MSXML2.DOMDocument60

objXMLDoc.loadXML ("C:\Users\LW\Desktop\formatted.xml")

XmlNamespaces = "xmlns:ns2='http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd' xmlns:ns1='http://mws.amazonservices.com/schema/Products/2011-10-01'"

objXMLDoc.SetProperty "SelectionNamespaces", XmlNamespaces

Set objXMLNode1 = objXMLDoc.selectSingleNode("//ns2:Price/ns2:LandedPrice/ns2:Amount")

MsgBox objXMLNode1(0).text

End Function

As you can see, I seek to extract the XML value (showing in the XML as 14.45)

I've spent a good while googling this to no avail, but settled on the above code aftr reading this 'SelectSingleNode' XPath query from XML with multiple Namespaces

Any ideas why I'm getting the error?

What line is giving the error? What is `XmlNamespaces`? It isn't declared. Try using `Option Explicit` and edit your question after you have declared all variables and fixed any typos. — John Coleman, Feb 22 '16 at 23:58
Hello John, it's the line **MsgBox objXMLNode1(0).text** that is giving the error. Re declaring XMLNameSpace ...good point! — peskywinnets, Feb 23 '16 at 00:41
@peskywinnets you're using the wrong prefix. Try with `//ns1:Price/ns1:LandedPrice/ns1:Amount` instead ... — har07, Feb 23 '16 at 00:56
Thanks but changing to **//ns1:Price/ns1:LandedPrice/ns1:Amount** still results in the same error. — peskywinnets, Feb 23 '16 at 00:59

Parfait · Answer 1 · 2016-02-23T00:59:31.047

Currently, there are several issues to your code:

objXMLDoc and XmlNamespaces are not declared.
LoadXML() is used for xml strings while Load() is for external files.
You use a namespace ns2 in xpath query expression when the tags do not currently have such a prefix.
You declare IXMLDOMNodeList object but use selectSingleNode() method and additionally use a list index for the text value. This raises a type mismatch error.
With undeclared namespaces using MSXML v6.0, you must declare the namespace and reference it in xpath expression.

Consider the below adjusted code using NodeList():

Public Function READXML()
    Dim objxmldoc As New MSXML2.DOMDocument60
    Dim objXMLNode1 As MSXML2.IXMLDOMNodeList
    Dim xmlNamespaces As String

    objxmldoc.Load ("C:\Users\LW\Desktop\formatted.xml")
    xmlNamespaces = "xmlns:ns2='http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd' " _
                      & "xmlns:ns1='http://mws.amazonservices.com/schema/Products/2011-10-01'"

    objxmldoc.setProperty "SelectionNamespaces", xmlNamespaces
    Set objXMLNode1 = objxmldoc.SelectNodes("//ns1:Price/ns1:LandedPrice/ns1:Amount")

    MsgBox objXMLNode1(0).Text

    Set objxmldoc = Nothing

End Function

Alternatively, with the SelectSingleNode() method:

Public Function READXML()
    Dim objxmldoc As New MSXML2.DOMDocument60
    Dim objXMLNode1 As MSXML2.IXMLDOMElement
    Dim xmlNamespaces As String

    objxmldoc.Load ("C:\Users\LW\Desktop\formatted.xml")
    xmlNamespaces = "xmlns:ns2='http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd' " _
                          & "xmlns:ns1='http://mws.amazonservices.com/schema/Products/2011-10-01'"

    objxmldoc.setProperty "SelectionNamespaces", xmlNamespaces
    Set objXMLNode1 = objxmldoc.SelectSingleNode("//ns1:Price/ns1:LandedPrice/ns1:Amount")        

    MsgBox objXMLNode1.Text

    Set objxmldoc = Nothing

End Function

Hello Parfait,I've tried both your code blocks, but I've had to change you Dim objxmldoc As New MSXML2.DOMDocument .....TO ....... Dim objxmldoc As New MSXML2.DOMDocument60 (because presumably I'm using a later version of Microsoft XML ...V6.0) ...but I still get the same error — peskywinnets, Feb 23 '16 at 00:35
Just to clarify about the error, both your suggestions error at the **Msgbox** line with **''Object variable or With block reference not set'** — peskywinnets, Feb 23 '16 at 00:47
See update. With MSXML 6.0 you must reference the undeclared namespace which you declared as: `ns1`. I add a #5 mentioning this. Similar to what @har07 mentioned. — Parfait, Feb 23 '16 at 01:01
Thanks - just tried your edited code (both versions), but still getting the same error at the MsgBox line :-( — peskywinnets, Feb 23 '16 at 01:05
This code works in Excel 2013. Be sure your file path points to correct location. — Parfait, Feb 23 '16 at 01:07
I beg your pardon - your edited code now works - thanks a million :-) — peskywinnets, Feb 23 '16 at 01:08

score 1 · Answer 2 · answered Feb 23 '16 at 00:42

1

The XML elements mentioned in your XPath are in the following default namespace :

xmlns="http://mws.amazonservices.com/schema/Products/2011-10-01"

Besides some good suggestions provided in the other answer, you should've used ns1 prefix, that you have declared to reference the default namespace, in your XPath :

XmlNamespaces = "xmlns:ns2='http://mws.amazonservices.com/schema/Products/2011-10-01/default.xsd' xmlns:ns1='http://mws.amazonservices.com/schema/Products/2011-10-01'"

objXMLDoc.SetProperty "SelectionNamespaces", XmlNamespaces

Set objXMLNode1 = objXMLDoc.selectSingleNode("//ns1:Price/ns1:LandedPrice/ns1:Amount")

answered Feb 23 '16 at 00:42

har07

88,338
12
84
137

Thanks - I tried your suggestion, but it didn't work for me. – peskywinnets Feb 23 '16 at 01:02
@peskywinnets - this does work per my latest comment but you also have to clean up the code as listed like the mismatched object references. – Parfait Feb 23 '16 at 01:05

VBA XML parsing ''Object variable or With block reference not set'

2 Answers2