Vba xpath in vba in Excel

XPath is a syntax for defining parts of an XML document

XPath uses path expressions to select nodes or node-sets in an XML document

XPath expressions can be used in JavaScript, Java, XML Schema, PHP, Python, C and C++, and lots of other languages.

XPath contains a library of standard functions

XPath can be used to navigate through elements and attributes in an XML document.

With the help of various elements we saw in the previous chapter, let us see how to read a XML document.

Consider the following XML document as an Example.

  1. <?xml�version="1.0"�encoding="UTF-8"?>
  2. <Agencies>
  3. <Contractor>
  4. excel vba xpath in vba

  5. ����<name>Bhandhan Servicing</name>
  6. ����<service_type>Marriage arrangement</service_type>
  7. ����<description>
  8. All Services related to marriages
  9. ���</description>
  10. </Contractor>
  11. <Contractor>
  12. ����<name>VS functions</name>
  13. ����<service_type>Party arrangements</service_type>
  14. ����<description>
  15. Official get together, birthday party arrangements.
  16. ����</description>
  17. </Contractor>
  18. <Contractor>
  19. ����<name>Swami services</name>
  20. ����<service_type>Spiritual Services</service_type>
  21. ����<description>
  22. ��� Ganapathi homam, New home poojas performed.
  23. ����</description>
  24. </Contractor>
  25. <Contractor>
  26. ����<name>B Plus Services</name>
  27. ����<service_type>Blood Donation</service_type>
  28. ����<description>
  29. ��� Conducts Blood donation camps, organizing emergency blood donation
  30. ����</description>
  31. ����<calories>600</calories>
  32. </Contractor>
  33. </Agencies>

The following snippet shows how to traverse and read data from the above XML.

  1. Sub TestXML()
  2. excel vba xpath in vba

  3. ����Dim xml_doc As Object
  4. ����Set xml_doc = CreateObject("MSXML2.DOMDocument")
  5. ����xml_doc.async = False: xml_doc.validateOnParse = False
  6. ����xml_doc.Load (<xml_file_name>)
  7. ����'Get Document Elements
  8. ����Set lists = xml_doc.DocumentElement
  9. ����'Get first child ( same as ChildNodes(0) )
  10. ����Set getFirstChild = lists.FirstChild
  11. ����'Print first child XML
  12. ����Debug.Print getFirstChild.XML
  13. ����'Print first child Text
  14. ����Debug.Print getFirstChild.Text
  15. ����Set xml_doc = Nothing
  16. End Sub


You can find similar Excel Questions and Answer hereunder

1) How can I remove display of Gridlines in my worksheet using VBA?

2) How can worksheet functions be accessed in VBA?

3) How do i put double quotes in a string in vba in Excel

4) How to delete rows with Excel VBA

5) How can I prevent users from seeing / accessing my macro code?

6) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

7) How to display messages boxes in VBA with the msgbox function

8) Introduction to tables in Excel VBA and how to use tables, create tables in VBA

9) How to code more simply in VBA. Use of Keywords is helping a lot

10) How do I enter a formula in a cell using VBA (using Relative Reference)?


Here the previous and next chapter