Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Read Data from XML File

To Read Data from XML File using in Microsoft Excel, you need to follow the steps below:

  • Create the object of “Microsoft XML Parser” ) (Microsoft.XMLDOM is the COM object of Microsoft XML Parser)
  • Load the XML from a specified path.
  • Select the tag from the XML file using SelectNodes or SelectSingleNode.

o   SelectNodes – Selects a list of nodes matches the Xpath pattern.

o   SelectSingleNode – Selects the first XMLNode that matches the pattern.

  • Iterate through all the Node by using Nodes.length and NodeValue.
  • Read the attributes by using Attribute.Length and getAttribute.
  • Read the particular index value from the XML File
  • Get all the values of particular type of nodes.

Sample XML: (Sample File has been taken from- https://msdn.microsoft.com/en-us/library/ms762271%28v=vs.85%29.aspx )

Read XML -1

Read XML -1

  • Create the object of “Microsoft XML Parser” ) (Microsoft.XMLDOM is the COM object of Microsoft XML Parser)

Set oXMLFile = CreateObject(“Microsoft.XMLDOM”)

  • Load the XML from a specified path.

               XMLFileName = “D:\Sample.xml”    

               oXMLFile.Load (XMLFileName)

  • Select the tag from the XML file using SelectNodes or SelectSingleNode.

             SelectNodes – Selects a list of nodes matches the Xpath pattern.

             Set TitleNodes = oXMLFile.SelectNodes(“/catalog/book/title/text()”)

             SelectSingleNode – Selects the first XMLNode that matches the pattern.

             Set Nodes_Particular = oXMLFile.SelectSingleNode(“/catalog/book[4]/title/text()”)

  • Iterate through all the Node by using Nodes.length and NodeValue.

For i = 0 To (TitleNodes.Length – 1)

Title = TitleNodes(i).NodeValue

  • Read the attributes by using Attribute.Length and getAttribute.

Set Nodes_Attribute = oXMLFile.SelectNodes(“/catalog/book”)

For i = 0 To (Nodes_Attribute.Length – 1)

Attributes = Nodes_Attribute(i).getAttribute(“id”)

  • Read the particular index value from the XML File

oXMLFile.SelectSingleNode(“/catalog/book[4]/title/text()”)

  • Get all the values of particular type of nodes.

oXMLFile.SelectNodes(“/catalog/book/title[../genre = ‘Fantasy’]/text()”)

NOTE:

Reference needed:

How to add “Microsoft Forms 2.0 Object Library”
Microsoft Office 12.0 Object Library

Complete Code:

 

Read XML

Read XML

'Get the 5th book title
Set Nodes_Particular = oXMLFile.SelectSingleNode("/catalog/book[4]/title/text()")' index starts with 0, so put 4 for 5th book
MsgBox "5th Book Title : " & Nodes_Particular.NodeValue

Get Book title

'Get all the Fantasy books

Set Nodes_Fantasy = oXMLFile.SelectNodes("/catalog/book/title[../genre = 'Fantasy']/text()")
mainWorkBook.Sheets("Sheet3").Range("A1").Value = "Fantasy Books"

mainWorkBook.Sheets("Sheet3").Range("A1").Interior.ColorIndex = 40

mainWorkBook.Sheets("Sheet3").Range("A1").Borders.Value = 1

' get their titles

For i = 0 To (Nodes_Fantasy.Length - 1)

Title = Nodes_Fantasy(i).NodeValue

mainWorkBook.Sheets("Sheet3").Range("A" & i + 2).Value = Title

Next

Read XML -2

You may also like...

32 Responses

  1. New User says:

    Can you post the entire Complete Code as opposed to just a snippet?

    • Sumit Jain says:

      Its a complete working code, just that its divided into snippets, if you copy paste the enitre code it will work ( including all snippets).
      Please let me know if you still need clarifications.

      Sumit

      • Robert says:

        Hey,

        I don’t think it is – there is no End Function in any of the snippets. After running the first snippet (Complete Code), we are not able to see the same output array. All we see are the titles of each column and no data in them.

        • Sumit Jain says:

          I just tested the code , its working fine for me. ‘End Function’ was removed becoz the code was getting continued in different snippets.

          NOTE : Make sure you provide the complete XML path
          XMLFileName = “C:\Users\Sumit Jain\Sample.xml” —- updated this line.

          I just ran this code —-

          Sub ReadXML()
          Call fnReadXMLByTags
          End Sub

          Function fnReadXMLByTags()
          Dim mainWorkBook As Workbook
          Set mainWorkBook = ActiveWorkbook
          mainWorkBook.Sheets(“Sheet1”).Range(“A:A”).Clear
          Set oXMLFile = CreateObject(“Microsoft.XMLDOM”)
          XMLFileName = “C:\Users\Sumit Jain\Google Drive\Excel Blog pics\XML\Sample.xml”
          oXMLFile.Load (XMLFileName)
          Set TitleNodes = oXMLFile.SelectNodes(“/catalog/book/title/text()”)
          Set PriceNodes = oXMLFile.SelectNodes(“/catalog/book/price/text()”)
          mainWorkBook.Sheets(“Sheet1”).Range(“A1,B1,C1”).Interior.ColorIndex = 40
          mainWorkBook.Sheets(“Sheet1”).Range(“A1,B1,C1”).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & 1).Value = “Book ID”
          mainWorkBook.Sheets(“Sheet1”).Range(“B” & 1).Value = “Book Titles”
          mainWorkBook.Sheets(“Sheet1”).Range(“C” & 1).Value = “Price”
          mainWorkBook.Sheets(“Sheet1”).Range(“D1”).Value = “Total books: ” & TitleNodes.Length

          For i = 0 To (TitleNodes.Length – 1)
          Title = TitleNodes(i).NodeValue
          Price = PriceNodes(i).NodeValue
          mainWorkBook.Sheets(“Sheet1”).Range(“B” & i + 2).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“C” & i + 2).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“B” & i + 2).Value = Title
          mainWorkBook.Sheets(“Sheet1”).Range(“C” & i + 2).Value = Price
          Next
          ‘Reading the Attributes
          Set Nodes_Attribute = oXMLFile.SelectNodes(“/catalog/book”)
          For i = 0 To (Nodes_Attribute.Length – 1)
          Attributes = Nodes_Attribute(i).getAttribute(“id”)
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & i + 2).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & i + 2).Value = Attributes
          Next
          End Function

          If still you are not able run it, please send me the code i will look into it

          Sumit

          • Robert says:

            Shouldn’t there be another For loop to write all the data into the sheet? From your code (which works as previously seen), we only see the titles of each column. There is no data in them though – nothing from the XML file.

        • Sumit Jain says:

          Hello Robert,

          Here is the description –

          code below creates the titles in Excel file –

          mainWorkBook.Sheets(“Sheet1”).Range(“A1,B1,C1”).Interior.ColorIndex = 40
          mainWorkBook.Sheets(“Sheet1”).Range(“A1,B1,C1”).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & 1).Value = “Book ID”
          mainWorkBook.Sheets(“Sheet1”).Range(“B” & 1).Value = “Book Titles”
          mainWorkBook.Sheets(“Sheet1”).Range(“C” & 1).Value = “Price”
          mainWorkBook.Sheets(“Sheet1”).Range(“D1”).Value = “Total books: ” & TitleNodes.Length

          Now This for loop will fetches all the book titles and price

          For i = 0 To (TitleNodes.Length – 1)
          Title = TitleNodes(i).NodeValue
          Price = PriceNodes(i).NodeValue
          mainWorkBook.Sheets(“Sheet1”).Range(“B” & i + 2).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“C” & i + 2).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“B” & i + 2).Value = Title
          mainWorkBook.Sheets(“Sheet1”).Range(“C” & i + 2).Value = Price
          Next

          Another for loop will get all the attributes names in XML
          ‘Reading the Attributes
          Set Nodes_Attribute = oXMLFile.SelectNodes(“/catalog/book”)
          For i = 0 To (Nodes_Attribute.Length – 1)
          Attributes = Nodes_Attribute(i).getAttribute(“id”)
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & i + 2).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & i + 2).Value = Attributes
          Next

          is it clarified now? Please feel free to ask if its not

          Sumit

          • Robert says:

            Hey Sumit,

            What you’re saying makes sense now. But I am trying to figure out why my output is this:

            Book ID Book Titles Price Total Books:0

            I am using a XML file that is the same as yours – I just added a total of 12 books and retyped your screenshot.

            Maybe there is a reference I need? I am using Microsoft XML v6.0 too.

          • Sumit Jain says:

            I have just mailed you the files which i have used, try those.

            Sumit

          • Robert says:

            Hey Sumit,

            It works! Thank you very much for your help – I can learn a lot from this example!

            Thanks again, I appreciate it.

          • Sumit Jain says:

            Hello Robert,
            I am glad that this was helpful for you.
            You are always welcome here. Your appreciation means a lot.

            Thanks
            Sumit

      • New User says:

        How do you write attributes into a certain cell?

  2. Robert says:

    Very helpful – but the full code would be even better

  3. charan says:

    Hi Sumit,

    I just tested the code , I am getting error “subscript out of range error” at
    mainWorkBook.Sheets(“Sheet1”).Range(“A:A”).Clear line.
    can you tell me why I am getting this.

    Thanks,
    charan.

    • Sumit Jain says:

      Hello Charan,

      just checked, that line is working fine for me. That line is clearing the entire column A in sheet 1.
      few quick point to check – make sure Set mainWorkBook = ActiveWorkbook is working fine. Please revert back if problem still persist.

      Thanks
      Sumit Jain

      • Sabariganesh J says:

        Hi,

        Thanks for posting this. Very useful for me to easily re-write to my needs.

        To answer the above question. It is not working if I copy paste from your code. To test your snippet I had to retype the above contents.

        Regards,
        Sabari

        • Sumit Jain says:

          Hello Sabari,

          I am glad that you find this post useful. I will re-check to make sure that further no one has to retype the code before using it.

          Thanks
          Sumit J

      • karan jain says:

        hey sumit,
        I am getting the same error ” subscipt out of range” at at
        mainWorkBook.Sheets(“Sheet1”).Range(“A:A”).Clear line.

        my Set main­Work­Book = Active­Work­book is work­ing fine in debugging.
        Can you plase help me with this

  4. Sabariganesh J says:

    Hi Sumit,

    I would like to parse an xml which has 2 levels of nodelist. For ex:

    ……

    I need to parse through each Books in each Bookshelf one by one. I am not able to get this by using

    oXMLFile.SelectNodes(“/../Bookshelf[i]/Books/text()”)

    Can you please help me out for this.

    Thanks
    Sabari

  5. Phil says:

    Hi,

    Thanks for the above it works perfectly, however the XML file I want to read into excel has a namespace and with a namespace the code doesn’t work..

    Any idea?

  6. Dion says:

    what refference do u need? I have office 2010

    • SJ says:

      I am using these references for this code:

      “Microsoft Forms 2.0 Object Library”
      Microsoft Office 12.0 Object Library

      updated the post as well, thanks for pointing out.

  7. anu says:

    while using above code getting below error in Mac
    Run-time error 429 ActiveX component can’t create object

  8. tran viet says:

    Could you send me above xml file. I cannot find it here. Thanks

  9. S O says:

    Hi SJ
    This is a really useful post. I got started great and I was stuck because of my source XML file
    Problem:
    I have 3 Nodes
    Step Title, Status and Comments
    I always have Step Title and Status but comments is optional. So I ended up with uneven arrays and comments are not aligned with the proper steps using Comment = CommentNodes(i).NodeValue. Could you please help?

    Thanks in advance.

    Example Steps with no comments



  10. Damien says:

    Thank you sooo much! Perfect work, clear explanations, you just made my day!

  11. Tom says:

    Thanks. This all works fine until I close and re-open my workbook. If I run the macro again, it deletes column A but does not add the info back in again… Any ideas?

    • SJ says:

      Apologies for the late reply. may be code for saving the changes is missing. you can add ActiveWorkBook.Save at the end. If this does not solve the problem, let me know.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: