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
– John Coleman, Commented Feb 22, 2016 at 23:58
Hello John, it's the line MsgBox objXMLNode1(0).text that is giving the error. Re declaring XMLNameSpace ...good point! — peskywinnets
– peskywinnets, Commented Feb 23, 2016 at 0:41
@peskywinnets you're using the wrong prefix. Try with //ns1:Price/ns1:LandedPrice/ns1:Amount instead ... — har07
– har07, Commented Feb 23, 2016 at 0:56
Thanks but changing to //ns1:Price/ns1:LandedPrice/ns1:Amount still results in the same error. — peskywinnets
– peskywinnets, Commented Feb 23, 2016 at 0:59

har07 · Accepted Answer · 2016-02-23 00:42:08Z

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, 2016 at 0:42

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

peskywinnets Over a year ago

Thanks - I tried your suggestion, but it didn't work for me.

Parfait Over a year ago

@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 · Accepted Answer · 2016-02-23 00:59:31Z

1

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

edited Feb 23, 2016 at 0:59

answered Feb 23, 2016 at 0:23

Parfait

108k19 gold badges103 silver badges138 bronze badges

6 Comments

peskywinnets Over a year ago

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 Over a year ago

Just to clarify about the error, both your suggestions error at the Msgbox line with ''Object variable or With block reference not set'

Parfait Over a year ago

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.

peskywinnets Over a year ago

Thanks - just tried your edited code (both versions), but still getting the same error at the MsgBox line :-(

Parfait Over a year ago

This code works in Excel 2013. Be sure your file path points to correct location.

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related