Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 158
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Read data from anohter XLS file John Whitehead via OfficeKB.com Excel Worksheet Functions 6 July 31st 05 10:20 PM
Read data from a text file (*.txt) Adrian T Excel Programming 0 June 4th 04 10:00 PM
Read data from ascii file Nazrul Excel Programming 2 October 23rd 03 08:21 PM
VBA to read data from XL and import into another XL file Steve D[_4_] Excel Programming 0 August 28th 03 04:34 PM
Selective read data from ascii file Steve Kim Excel Programming 0 August 20th 03 05:19 AM


All times are GMT +1. The time now is 10:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"