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 fol­low the steps below:

  • Cre­ate the object of “Microsoft XML Parser” ) (Microsoft.XMLDOM is the COM object of Microsoft XML Parser)
  • Load the XML from a spec­i­fied path.
  • Select the tag from the XML file using SelectN­odes or SelectS­in­gleN­ode.

o   SelectN­odes – Selects a list of nodes matches the Xpath pattern.

o   SelectS­in­gleN­ode – Selects the first XMLN­ode that matches the pattern.

  • Iter­ate through all the Node by using Nodes.length and Node­Value.
  • Read the attrib­utes by using Attribute.Length and getAt­tribute.
  • Read the par­tic­u­lar index value from the XML File
  • Get all the val­ues of par­tic­u­lar type of nodes.

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

Read XML -1

Read XML –1

  • Cre­ate the object of “Microsoft XML Parser” ) (Microsoft.XMLDOM is the COM object of Microsoft XML Parser)

Set oXML­File = CreateObject(“Microsoft.XMLDOM”)

  • Load the XML from a spec­i­fied path.

               XML­File­Name = “D:\Sample.xml”    

               oXMLFile.Load (XMLFileName)

  • Select the tag from the XML file using SelectN­odes or SelectS­in­gleN­ode.

             SelectN­odes – Selects a list of nodes matches the Xpath pattern.

             Set TitleN­odes = oXMLFile.SelectNodes(“/catalog/book/title/text()”)

             SelectS­in­gleN­ode – Selects the first XMLN­ode that matches the pattern.

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

  • Iter­ate through all the Node by using Nodes.length and Node­Value.

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

Title = TitleNodes(i).NodeValue

  • Read the attrib­utes by using Attribute.Length and getAt­tribute.

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

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

Attrib­utes = Nodes_Attribute(i).getAttribute(“id”)

  • Read the par­tic­u­lar index value from the XML File

oXML­File.SelectS­in­gleN­ode(“/catalog/book[4]/title/text()”)

  • Get all the val­ues of par­tic­u­lar type of nodes.

oXML­File.SelectN­odes(“/catalog/book/title[../genre = ‘Fan­tasy’]/text()”)

NOTE:

Ref­er­ence needed:

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

Com­plete 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 Com­plete Code as opposed to just a snippet?

    • Sumit Jain says:

      Its a com­plete work­ing code, just that its divided into snip­pets, if you copy paste the eni­tre code it will work ( includ­ing all snip­pets).
      Please let me know if you still need clarifications.

      Sumit

      • Robert says:

        Hey,

        I don’t think it is — there is no End Func­tion in any of the snip­pets. After run­ning the first snip­pet (Com­plete Code), we are not able to see the same out­put array. All we see are the titles of each col­umn and no data in them.

        • Sumit Jain says:

          I just tested the code , its work­ing fine for me. ‘End Func­tion’ was removed becoz the code was get­ting con­tin­ued in dif­fer­ent snippets.

          NOTE : Make sure you pro­vide the com­plete XML path
          XML­File­Name = “C:\Users\Sumit Jain\Sample.xml” —- updated this line.

          I just ran this code —-

          Sub Read­XML()
          Call fnRead­XML­By­Tags
          End Sub

          Func­tion fnRead­XML­By­Tags()
          Dim main­Work­Book As Work­book
          Set main­Work­Book = Active­Work­book
          mainWorkBook.Sheets(“Sheet1”).Range(“A:A”).Clear
          Set oXML­File = CreateObject(“Microsoft.XMLDOM”)
          XML­File­Name = “C:\Users\Sumit Jain\Google Drive\Excel Blog pics\XML\Sample.xml“
          oXMLFile.Load (XML­File­Name)
          Set TitleN­odes = oXMLFile.SelectNodes(“/catalog/book/title/text()”)
          Set Pri­ceN­odes = 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
          ‘Read­ing the Attrib­utes
          Set Nodes_Attribute = oXMLFile.SelectNodes(“/catalog/book”)
          For i = 0 To (Nodes_Attribute.Length — 1)
          Attrib­utes = Nodes_Attribute(i).getAttribute(“id”)
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & i + 2).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & i + 2).Value = Attrib­utes
          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 pre­vi­ously seen), we only see the titles of each col­umn. There is no data in them though — noth­ing from the XML file.

        • Sumit Jain says:

          Hello Robert,

          Here is the description -

          code below cre­ates 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 attrib­utes names in XML
          ‘Read­ing the Attrib­utes
          Set Nodes_Attribute = oXMLFile.SelectNodes(“/catalog/book”)
          For i = 0 To (Nodes_Attribute.Length — 1)
          Attrib­utes = Nodes_Attribute(i).getAttribute(“id”)
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & i + 2).Borders.Value = 1
          mainWorkBook.Sheets(“Sheet1”).Range(“A” & i + 2).Value = Attrib­utes
          Next

          is it clar­i­fied now? Please feel free to ask if its not

          Sumit

          • Robert says:

            Hey Sumit,

            What you’re say­ing makes sense now. But I am try­ing to fig­ure out why my out­put 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 ref­er­ence 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 appre­ci­ate it.

          • Sumit Jain says:

            Hello Robert,
            I am glad that this was help­ful for you.
            You are always wel­come here. Your appre­ci­a­tion means a lot.

            Thanks
            Sumit

      • New User says:

        How do you write attrib­utes into a cer­tain cell?

  2. Robert says:

    Very help­ful — but the full code would be even better

  3. charan says:

    Hi Sumit,

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

    Thanks,
    charan.

  4. Sabariganesh J says:

    Hi Sumit,

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

    .…..

    I need to parse through each Books in each Book­shelf 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 per­fectly, how­ever the XML file I want to read into excel has a name­space and with a name­space the code doesn’t work..

    Any idea?

  6. Dion says:

    what ref­fer­ence do u need? I have office 2010

    • SJ says:

      I am using these ref­er­ences for this code:

      Microsoft Forms 2.0 Object Library”
      Microsoft Office 12.0 Object Library

      updated the post as well, thanks for point­ing out.

  7. anu says:

    while using above code get­ting below error in Mac
    Run-time error 429 ActiveX com­po­nent can’t cre­ate object

  8. tran viet says:

    Could you send me above xml file. I can­not find it here. Thanks

  9. S O says:

    Hi SJ
    This is a really use­ful post. I got started great and I was stuck because of my source XML file
    Prob­lem:
    I have 3 Nodes
    Step Title, Sta­tus and Com­ments
    I always have Step Title and Sta­tus but com­ments is optional. So I ended up with uneven arrays and com­ments are not aligned with the proper steps using Com­ment = CommentNodes(i).NodeValue. Could you please help?

    Thanks in advance.

    Exam­ple Steps with no comments

    -

    -

  10. Damien says:

    Thank you sooo much! Per­fect work, clear expla­na­tions, you just made my day!

  11. Tom says:

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

    • SJ says:

      Apolo­gies for the late reply. may be code for sav­ing the changes is miss­ing. you can add ActiveWorkBook.Save at the end. If this does not solve the prob­lem, let me know.

Leave a Reply

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

%d bloggers like this: