Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
read data element from XML file
I've got a Excel worksheet that needs to lookup data values in a XML file.
If the XML file is formated as such: File_1 ParentNode1 ChildNode1 DataElement1 DataElement2 DataElement3 ChildNode2 DataElement1 DataElement2 ParentNode2 ChildNode1 DataElement1 DataElement2 DataElement3 ChildNode2 DataElement1 DataElement2 How would I lookup the data value in ParentNode2\ChildNode1\DataElement2? I am using a reference to Microsoft XML v.4.0 in the code explorer for this workbook. Thanks In Advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
read data element from XML file
Hi Steve,
I haven't played with this a year or so and no longer have the XML SDK installed on this machine. I've dug out an old workbook with some code in though. There's a few ways. If you have the schema, you can address it directly as an index I believe e.g. xmlSch.documentElement.childNodes(2).childNodes(1) .item(2).text or if you know the name of the item: Set root = xmlDoc.documentElement Set oNodeList = root.childNodes For Each Item In oNodeList 'do Artist Set subItem = Item.selectSingleNode("./s:Artist") myData(0, 0) = subItem.Attributes.getNamedItem("sortName").Text 'do Song info myData(0, 1) = Item.Attributes.getNamedItem("title").Text myData(0, 2) = Item.Attributes.getNamedItem("ID").Text next item 'etc. At least this is how I used to do it. I've pasted some code (with bits edited out and amended) that might help you too. I've also code somewhere that does a better, more generic job of loading data from XMLs. I can have a look for it if you like. HTH, Gareth Sub ImportXMLfile() Dim xmlDoc As New MSXML2.DOMDocument Dim root As IXMLDOMElement Dim oNodeList As IXMLDOMNodeList Dim Item As IXMLDOMNode Dim wb As Workbook Dim subItem As IXMLDOMNode Dim myData(0, 0 To 10) As String Dim iRow As Long Dim j As Integer xmlDoc.async = False 'Try and load our main file If Not xmlDoc.Load(myPath & "file.xml") Then MsgBox "Error loading" Exit Sub End If ' create a workbook Set wb = Workbooks.Add Set root = xmlDoc.documentElement Set oNodeList = root.childNodes iRow = 1 With wb.Sheets(1) For Each Item In oNodeList iRow = iRow + 1 On Error Resume Next 'do Artist and song Set subItem = Item.selectSingleNode("./s:Artist") myData(0, 0) = _ subItem.Attributes.getNamedItem("sortName").Text myData(0, 1) = Item.Attributes.getNamedItem("title").Text myData(0, 2) = Item.Attributes.getNamedItem("ID").Text myData(0, 3) = Item.Attributes.getNamedItem("category").Text 'do Song info Set subItem = Item.selectSingleNode("./s:Album") myData(0, 8) = subItem.Attributes.getNamedItem("title").Text 'do Additional Set subItem = Item.selectSingleNode("./s:SongExtra") myData(0, 9) = subItem.Attributes.getNamedItem("label").Text On Error GoTo 0 Range(.Cells(iRow, 1), .Cells(iRow, 11)) = myData DoEvents 'blank out the array For j = 0 To 10 myData(0, j) = "" Next j Next End With Set oNodeList = Nothing Set subItem = Nothing Set root = Nothing Set wb = Nothing End sub Steve Moreno wrote: I've got a Excel worksheet that needs to lookup data values in a XML file. If the XML file is formated as such: File_1 ParentNode1 ChildNode1 DataElement1 DataElement2 DataElement3 ChildNode2 DataElement1 DataElement2 ParentNode2 ChildNode1 DataElement1 DataElement2 DataElement3 ChildNode2 DataElement1 DataElement2 How would I lookup the data value in ParentNode2\ChildNode1\DataElement2? I am using a reference to Microsoft XML v.4.0 in the code explorer for this workbook. Thanks In Advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
read data element from XML file
On Monday, August 8, 2005 at 8:24:12 PM UTC+3, Steve Moreno wrote:
I've got a Excel worksheet that needs to lookup data values in a XML file. If the XML file is formated as such: File_1 ParentNode1 ChildNode1 DataElement1 DataElement2 DataElement3 ChildNode2 DataElement1 DataElement2 ParentNode2 ChildNode1 DataElement1 DataElement2 DataElement3 ChildNode2 DataElement1 DataElement2 How would I lookup the data value in ParentNode2\ChildNode1\DataElement2? I am using a reference to Microsoft XML v.4.0 in the code explorer for this workbook. Thanks In Advance. Hello Steve, I have a small task I want to read XML file and insert tags as dynamic table bt macros ... Please, any help to start Copying code that you are inserted here but not working fine I changed the path of XML file... Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Read data from anohter XLS file | Excel Worksheet Functions | |||
Read data from a text file (*.txt) | Excel Programming | |||
Read data from ascii file | Excel Programming | |||
VBA to read data from XL and import into another XL file | Excel Programming | |||
Selective read data from ascii file | Excel Programming |