0

I need to retrieve data from a website in XML format and save it in an Access database. While I'm able to pull the data successfully, I'm having problems parsing it. Specifically, I only seem to be able to read the first record returned, and don't know how to cycle through the remaining records.

Here's an example of what the source XML looks like. This is an employment history for a specific individual, so the names, titles and date fields will change with each record. In this example, this individual has 7 employment records available, each with a different company.

<DataRow>
<DataItem name="Symbol">00123S-E</DataItem>
<DataItem name="Company ID">061ABC-E</DataItem>
<DataItem name="Company Ticker">@NA</DataItem>
<DataItem name="Company Name">L.L. Bean, Inc.</DataItem>
<DataItem name="Title">Independent Director</DataItem>
<DataItem name="Function Code">IND</DataItem>
<DataItem name="Function Description">Independent Dir/Board Member</DataItem>
<DataItem name="Start Date">20140508</DataItem>
<DataItem name="End Date">@NA</DataItem>
</DataRow>

And here's the Access VBA code that I thought would work:

Private Sub bNewFetch_Click()
    Dim xmldoc As MSXML2.DOMDocument
    Dim xmlElement As MSXML2.IXMLDOMElement
    Dim xmlSelection As MSXML2.IXMLDOMSelection
    Dim myLineItem As Object

    Set xmldoc = New MSXML2.DOMDocument
    xmldoc.async = False
    xmldoc.Load [Here's where the URL goes]

    Set xmlSelection = xmldoc.selectNodes("//DataRow")

    For Each xmlElement In xmlSelection
        Set myLineItem = xmldoc.selectNodes("//DataRow/DataItem")
        Debug.Print myLineItem(3).Text
    Next xmlElement
End Sub

Unfortunately, instead of returning the names of the 7 different companies this individual worked for, the code is simply returning "L.L. Bean, Inc." 7 times. It seems to know there are 7 records, but instead of stepping through those records and returning the relevant Company Name for each, it's just repeating the Company Name from the first record.

Can anyone advise on what I need to do to retrieve all data from each returned record, and not just the first?

Thanks in advance for any suggestions.

SOLUTION:

Thanks to BankBuilder for pointing out the problem with my retrieval loop. The solution was to replace the last For/Next statement with:

    For Each xmlElement In xmlSelection
        Debug.print xmlElement.selectSingleNode("DataItem[@name='Company Name']").Text
    Next xmlElement
1
  • 1
    myLineItem(0).Text is Symbol. myLineItem(1).Text is Company ID. myLineItem(2).Text is Company ID. What do you think myLineItem(3).Text should be? Commented Apr 19, 2019 at 0:11

1 Answer 1

1

You're only printing the first DataRow's Company Name because of this line in your loop:

Set myLineItem = xmldoc.selectNodes("//DataRow/DataItem")

It is pulling the data from the first element, regardless of your current element in the loop.

Replace the current code in your loop with Debug.print xmlElement.selectSingleNode("DataItem[@name='Company Name']").Text. This will print the company name for the DataItem in the current DataRow (aka xmlElement in loop)

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

4 Comments

Thanks for the suggestion. Unfortunately, when I make this change, I get a run-time error 91. "Object variable or With block variable not set". Any ideas on what could be causing that?
See my update, remove the leading "/" in selectSingleNode
Yes! Brilliant! That did the trick. I will update the question to reflect this. Thanks so much for your help! I've been futzing with this code for two weeks now without success. You've resolved a great deal of frustration from my life. Much appreciated!
I'm glad to hear it!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.