How to Import XML Data into Excel Sheets?

Method- 1

To import XML data into an Excel spreadsheet, you can follow these steps:

  1. Open the Excel file you want to import the data into
  2. Click the “Data” tab in the ribbon
  3. Click “From Other Sources” and select “From XML Data Import”
  4. Select the XML file you want to import and click “Import”
  5. Follow the steps in the XML Map Wizard to map the XML elements to the appropriate cells in the worksheet.
  6. After the wizard has completed, you should now see the imported XML data in the Excel worksheet.

Note: You can also use the “Data” tab and “Get Data” from “From XML” to import XML data. This will start the Power Query Editor, and you can use this tool to shape your data as desired before loading it into the workbook.

Method 2 – Import XML Data in Excel by using Macro

To import XML data into Excel using a macro, you can use the following VBA code:

  1. Open the Excel file you want to import the data into
  2. Press Alt + F11 to open the Visual Basic Editor
  3. In the editor, right-click on the workbook name in the project explorer and select “Insert” and then “Module”
  4. In the new module, paste the following code:
Sub ImportXML()
    Dim xmlMap As XmlMap
    Dim xmlData As XmlData
    Set xmlMap = ActiveWorkbook.XmlMaps.Add("file.xml")
    Set xmlData = xmlMap.Import("file.xml")
    xmlData.Save
End Sub
  1. Replace “file.xml” with the name of your XML file.
  2. Close the Visual Basic Editor and return to the Excel workbook
  3. Press Alt + F8 to open the Macro dialog box and select the “ImportXML” macro
  4. Click “Run” and the XML data should be imported into the active worksheet.

Note: Before running the macro, make sure that the XML file is accessible and that the path to the file is correct.

esyExcel

Gavesh S. is the founder and owner of EsyExcel.com. He has been a Microsoft Excel Expert since early 2012. With experience explained thousand of queries for companies including Microsoft and top other financial institutions. Theres is most uses of Business Analysis, People Management, Automation, Performance Reporting, Strategic Analysis, Project Management, Managing Programs, Account Management etc.  In 2021 he found EsyExcel.com to share his experiences with other who’s is looking to learn MS Excel. He is currently working on a book about working in the Learn Excel, expanding his skill set beyond the Excel Learning niche.