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

5. Replace “file.xml” with the name of your XML file.
6. Close the Visual Basic Editor and return to the Excel workbook
7. Press Alt + F8 to open the Macro dialog box and select the “ImportXML” macro
8. 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.