• Read XML using Excel VBA

    XML is the file format that is widely used to transfer data over internet or between 2 systems with different platforms. The most widely used & familiar xml file in the internet world is the Sitemap.xml. This file has the major links to a website.

    Other widely used file formats for data transfer are JSON, CSV. In this article, we are going to learn how to read the xml file using XML DOM (Data Object Model).

    Also Read: Create Keyboard Shortcut - to VBA Macro code - in Excel

    Excel VBA XML Parser

    Using this tutorial you can build a XML parser using Excel VBA. Lets start with this step by step procedure. Open an Excel Workbook & Open VB Editor by pressing Alt + F11. Then follow these important steps.

    1. Add reference to “Microsoft XML, V6.0” from Excel VB editor.
      • VB Editor -> Menu->Tools -> Reference
      • Scroll down till Microsoft XML, V2.0 or 3.0 or 6.0 appears. The version of XML depends on the OS & Office version installed in your machine.
      • Click Ok.

    2. Now, Copy paste the code to your VBE.
    3. Download a file from Internet or if you have a file already, Modify the xml file path in the code.
    4. Run the code by pressing F5.

    This code uses XML DOM model to parse each node from input xml file. Then write it to the Excel file one by one.

    Post Name
    VBA XML Parser

    Previous Post:
    Next Post: