Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing XML in VBA (Acc2K, Win2K, MSXML 2.0)

    I have a simple output page in xml with the following structure:

    <Answers>
    <AnswerSet>
    <Answer questionID="FName">First Name</Answer>
    <Answer questionID="LName">Lase Name</Answer>
    </AnswerSet>
    </Answers>

    Using MSXML2.0/VBA I can extract the value assigned to each <Answer> node but also want to extract the questionID attribute. So far haven't found a method...

    Any ideas?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Parsing XML in VBA (Acc2K, Win2K, MSXML 2.0)

    Does the Object Browser help? Not sure MSXML has a compatible type library.

    Adding a Stop statement after you acquire the object and poking around in the Locals window may help you find the attributes you need.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Parsing XML in VBA (Acc2K, Win2K, MSXML 2.0)

    You can do something like this:
    <pre>Sub ListNodesAndAttributes()
    Dim xmlDoc As New MSXML2.DOMDocument26
    Dim answers As MSXML2.IXMLDOMNodeList, answer As MSXML2.IXMLDOMElement
    Dim lngIndex As Long
    xmlDoc.async = False
    xmlDoc.Load "C:answers.xml"
    Set answers = xmlDoc.getElementsByTagName("Answer")
    For lngIndex = 0 To answers.length - 1
    Set answer = answers.Item(lngIndex)
    Debug.Print answer.Text; answer.getAttribute("questionID")
    Next lngIndex
    End Sub
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing XML in VBA (Acc2K, Win2K, MSXML 2.0)

    thanks - I tried this and it appears I need to set a reference to MX XML v 6.0 for starters. however I get another error trying to load the XML file. According to what I've read on MSDN, this level of XML appears to be aimed at Office 2000+, not 2000 itself, so I don't know if there's any other 'gotchas' in trying to use this solution.

    thanks!

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Parsing XML in VBA (Acc2K, Win2K, MSXML 2.0)

    I don't think the XML library much cares what program calls it.

    Do you want to give more specifics on the error message or were you planning to take a completely new approach?

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing XML in VBA (Acc2K, Win2K, MSXML 2.0)

    No, I'd really like to grab the questionID from the XML...

    But when I drop your code in and re-set the directory to the desired file, with reference set to MS XML v 2.0 I get 'User-defined Type not defined' on attempting
    New MSXML2.DOMDocument26

    with refernce set to v 6.0, one gets 'ActiveX componenet can't create object' on
    xmlDoc.async = False

    commenting this out means the same error on the next line
    xmlDoc.Load "path"

    so it looks like something in my ActiveX installation isn't up to snuff... that's where I noticed all this 2003 and beyond with respect to operating on
    MSXML2.DOMDocument26

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Parsing XML in VBA (Acc2K, Win2K, MSXML 2.0)

    <P ID="edit" class=small>(Edited by jscher2000 on 18-Jun-07 14:49. )</P>Maybe it's a typo in your reference materials. DOMDocument26 is a very strange object name.

    Added: I have used this in the past (late binding method):

    <code>Dim xmlDoc As Object, xmlRoot As Object, xmlCBarNode As Object
    ' Retrieve previously saved XML document
    Set xmlDoc = CreateObject("MSXML.DOMDocument")
    xmlDoc.Load "C:folderfolderOutlookCBars.xml"
    ' Loop through the saved settings and move the toolbars around
    Set xmlRoot = xmlDoc.documentElement
    For Each xmlCBarNode In xmlRoot.childNodes
    ...
    Next</code>

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Parsing XML in VBA (Acc2K, Win2K, MSXML 2.0)

    If you delete the declaration and type MSXML2 followed by the period, you should be able to see how to declare it for your version (probably as <code>MSXML2.DOMDocument20</code>)
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing XML in VBA (Acc2K, Win2K, MSXML 2.0)

    OK, that worked. Setting the object to
    Dim xmlDoc As New MSXML2.DOMDocument

    and adding a reference to MSXML v. 3 or greater gets me what I asked for.

    Thanks all!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •