Transfer data from XML DOM Document to spreadsheet

**skazis** · 04-06-2011, 04:45 PM

Hello,
I reached the place where I read in *.xml from the web as DOM document to access nodes and manipulate them, but I can't seem to find a way to get values from DOM document to spreadsheet. I used real link, so nodes can bee looked up there. I'd like to paste all "price" nodes in my spreadsheet "Sheet1", cells(1,1). Here is the code:

Sub FindNode()

Dim oXmlDoc As DOMDocument
Dim oXmlNode As IXMLDOMNode
Dim oXmlNodes As IXMLDOMNodeList

Set oXmlDoc = New DOMDocument
oXmlDoc.async = False
oXmlDoc.Load ("http://api.erepublik.com/v2/feeds/market/15/39/5")

Set oXmlNodes = oXmlDoc.SelectNodes("/offers/offer/price")
Debug.Print oXmlDoc.XML
For Each oXmlNode In oXmlNodes
Debug.Print oXmlNode.Text
Next

End Sub

**Chippy** · 04-06-2011, 06:00 PM

Originally Posted by skazis

I'd like to paste all "price" nodes in my spreadsheet "Sheet1", cells(1,1).

Within the For Next loop put:

Sheets("Sheet1").Cells(1, 1).Value = Sheets("Sheet1").Cells(1, 1).Value & " " & oXmlNode.Text

**skazis** · 04-06-2011, 06:37 PM

That did the trick. Thank you. And could you please tell how to put these values each in a new cell? I tried FOR loop and variable within Cells(i,1), but for each i loop cycled trough nodes and at the end put the last value and I ended up with all cells equal to last value of nodes.

**skazis** · 04-06-2011, 06:58 PM

I am getting better, did it on my own.

For Each oXmlNode In oXmlNodes
Sheets("Sheet1").Cells(i, 1).Value = oXmlNode.Text
Debug.Print oXmlNode.Text
i = i + 1
Next

Transfer data from XML DOM Document to spreadsheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Transfer data from XML DOM Document to spreadsheet

Re: Transfer data from XML DOM Document to spreadsheet

Re: Transfer data from XML DOM Document to spreadsheet

Re: Transfer data from XML DOM Document to spreadsheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions