object variable or with block variable not set error for XML selectSingleNode VBA

Question

I'm a bit of a novice (both on this site and coding in general) so please bear with me. I have an XML document that looks like this:

<?xml version="1.0" encoding="UTF-8"?>
  -<LearnerFirstTimeRegistration xmlns="urn:learnerregfeedback-schema"> 
     -<Header> 
       -<Record type="1">
         <Filename>blahblahblah.xml</Filename> 
         <ProvinceID>5</ProvinceID> 
         <EmisNumber>5xxxxxxx8</EmisNumber> 
         <StatusID>2</StatusID> 
         <TransactionCategoryID>4</TransactionCategoryID> 
         <SequenceNumber>1</SequenceNumber> 
         <DateCreated>2014-02-26</DateCreated> 
         <CreatedBy>blahblahblah</CreatedBy> 
        </Record> 
      </Header> 
    -<LearnerInformation> ....  etc etc

Using VBA I am simply trying to display in a msgbox the value in SequenceNumber node (in this case =1)

I'm using:

Private Sub Command317_Click()

 Dim objNodeList As IXMLDOMNodeList
 Dim objNode As IXMLDOMNode
 Dim xDoc As MSXML2.DOMDocument30

 Set xDoc = New MSXML2.DOMDocument30

 xDoc.LoadXML ("\\location\blahblahblah.xml")

 MsgBox xDoc.selectSingleNode("SequenceNumber").nodeName

End Sub

It is giving me a run time error: object variable or with block variable not set From what i've read around the net it seems to suggest that its because its trying to return a null value. but as you can see, SequenceNumber is not null, its 1.

Any help would be much appreciated

Tim Williams · Answer 1 · 2014-02-26T23:15:49.670

0

MsgBox xDoc.selectSingleNode("//SequenceNumber").nodeName

or

MsgBox xDoc.selectSingleNode("//SequenceNumber").Text

if you want the element content.

EDIT:

Here's the code which works for me:

Sub Test()

    Dim oDoc As New MSXML2.DOMDocument30
    Dim XML As String

    XML = ActiveSheet.Range("A1").Value

    oDoc.validateOnParse = True
    oDoc.LoadXML XML

    Debug.Print oDoc.SelectSingleNode("//SequenceNumber").Text '>> 1

End Sub

I'm loading this XML from a worksheet cell:

 <?xml version="1.0" encoding="UTF-8"?>
  <LearnerFirstTimeRegistration  xmlns="urn:learnerregfeedback-schema"> 
     <Header> 
       <Record type=""1"">
         <Filename>blahblahblah.xml</Filename> 
         <ProvinceID>5</ProvinceID> 
         <EmisNumber>5xxxxxxx8</EmisNumber> 
         <StatusID>2</StatusID> 
         <TransactionCategoryID>4</TransactionCategoryID> 
         <SequenceNumber>1</SequenceNumber> 
         <DateCreated>2014-02-26</DateCreated> 
         <CreatedBy>blahblahblah</CreatedBy> 
        </Record> 
      </Header> 
    </LearnerFirstTimeRegistration>

edited Feb 26 '14 at 23:15

answered Feb 26 '14 at 21:25

Tim Williams

154,628
8
97
125

Thank you kindly. I still get the same error. whether I use .nodename or .text . perhaps i should use something other than text because the data is a number? guessing here. – user3357684 Feb 26 '14 at 22:06
MsgBox xDoc.getElementsByTagName("//SequenceNumber").Item(0).Text – user3357684 Feb 26 '14 at 22:46
(sorry for multiple messages - i tried pressing cntrl enter to go to the next line and it kept posting message) if use above code it moans about the // characters, if i take those out i go back to object variable not set error and it tells me that objNode = nothing when i debug. i feel so close to getting it right but nothing i'm trying works. i cant see how its seeing nothing, im sure the answer is simple. ive checked the xml file itself and the Sequence number is 1. Could it be a 'field type'issue, i didnt think xml documents carried any formatting of field types. – user3357684 Feb 26 '14 at 22:56
oh my word, i knew it was going to be a small thing. thank you mate, appreciate your help but i went with my original code because i didnt want to involve spreadsheets or anything (or i didnt quite understand what you meant by worksheet cell) but i changed .nodename to .text as you suggested, which didnt work at first, then after watching a demo somewhere i saw someone get the same error and correct it by changing xDoc.loadXML ("......") to xDoc.load ("....."). I guess without formal training i would hit those hassles. and i apologise for my ignorance concerning the //, i understand it now. – user3357684 Feb 27 '14 at 15:07

object variable or with block variable not set error for XML selectSingleNode VBA

1 Answers1