download xml into excel

folks

how can I connver this code /xml to display in excel

Public Shared Sub Main()

        Dim doc As New XmlDocument()
        doc.LoadXml("<book ISBN='1-861001-57-5'>" & _
                    "<title>Pride And Prejudice</title>" & _
                    "<price>19.95</price>" & _
                    "</book>")

        Dim root As XmlNode = doc.FirstChild

        'Display the contents of the child nodes.
        If root.HasChildNodes Then
            Dim i As Integer
            For i = 0 To root.ChildNodes.Count - 1
                Console.WriteLine(root.ChildNodes(i).InnerText)
            Next i
        End If
    End Sub 'Main
rutgermonsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John-Charles-HerzbergCommented:
Manually Importing XML Data

1) Start Excel and then click Open on the File menu.

2) In the Files of type list, select XML files.

3) Select the XML file you want to open and then click Open.

4) If the file has multiple XSL style sheets, you are prompted to select one of the style sheets. Select a style sheet if you want to appy a style sheet. Or, click Cancel to not select a style sheet.
0
rutgermonsAuthor Commented:
im looking for automated, not manual,hence the macro requirement
0
aikimarkCommented:
Why bother using XML as an intermediate form?  If you have the data, just push it into Excel
    Dim rng As Range
    Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 3))
    rng.Value = Array("1-861001-57-5", "Pride And Prejudice", 19.95)

Open in new window

Read more in my Fast Data Push to Excel article:  http:A_2253.html
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

rutgermonsAuthor Commented:
i'm trying to see if the childnodes function works, thats what I need to see happening, my xml may have hundreds of children
0
aikimarkCommented:
One of the pieces of data isn't a child node.  It is an attribute.
0
aikimarkCommented:
This doesn't seem to be legal VBA syntax.  What is your development and run-time environment?
0
aikimarkCommented:
If solving this in the VBA environment, I would expect your code would look something like this:
Public Sub Main()
    Dim i As Integer
    'Dim vNames As Variant
    Dim doc As New MSXML2.DOMDocument
    Dim root As MSXML2.IXMLDOMNode
    Dim rng As Range
    
    Set rng = ActiveSheet.Cells(1, 1)
    'vNames = Array("Title:", "Price:")
    doc.loadXML "<book ISBN='1-861001-57-5'>" & _
                "<title>Pride And Prejudice</title>" & _
                "<price>19.95</price>" & _
                "</book>"

    Set root = doc.FirstChild
    'Display the contents of the child nodes.
    'Debug.Print "ISBN:", root.Attributes(0).Text
    rng.Value = root.Attributes(0).Text
    If root.hasChildNodes Then
        For i = 0 To root.childNodes.Length - 1
            rng.Offset(0, i + 1).Value = root.childNodes(i).Text
            'Debug.Print vNames(i), (root.childNodes(i).Text)
        Next i
    End If
    Set rng = rng.Offset(1) 'move down one row
End Sub 'Main

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rutgermonsAuthor Commented:
thanks! works with a little tweak as explained below

stackoverflow.com/questions/11245733/declaring-early-bound-msxml-object-throws-an-error-in-vba
0
aikimarkCommented:
I usually use late binding, rather than early binding.  In this case, I used early binding to more closely resemble your (?VB.Net?) code.
Here is a late-binding version of the code:
Public Sub Q_28490462()
    Dim i As Integer
    Dim doc As Object
    Dim root As Object
    Dim rng As Range
    Set doc = CreateObject("MSXML2.DOMDocument")
    
    Set rng = ActiveSheet.Cells(1, 1)
    'vNames = Array("Title:", "Price:")
    doc.loadXML "<book ISBN='1-861001-57-5'>" & _
                "<title>Pride And Prejudice</title>" & _
                "<price>19.95</price>" & _
                "</book>"

    Set root = doc.FirstChild
    'Display the contents of the child nodes.
    'Debug.Print "ISBN:", root.Attributes(0).Text
    rng.Value = root.Attributes(0).Text
    If root.hasChildNodes Then
        For i = 0 To root.childNodes.Length - 1
            rng.Offset(0, i + 1).Value = root.childNodes(i).Text
            'Debug.Print vNames(i), (root.childNodes(i).Text)
        Next i
    End If
    Set rng = rng.Offset(1) 'move down one row
End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.