[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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
0
rutgermons
Asked:
rutgermons
  • 5
  • 3
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now