Convert XML Files to Excel – 5 Methods to Import
To Import XML data to Excel you can use any of these listed methods.
- VBA to import XML to Excel
- Quick VBA to Convert XML into Excel
- VBA to read each nodes in XML
- VBA to Import XML from any website
- Directly Open XML in Excel.
- Data Import to Convert XML to XLSX
Lets review one by one & get free Excel macro code also to do this.
1. VBA Read XML file into Excel
1.1 Quick VBA To Convert XML to Excel
This methods import and open XML file in Excel from local path, but it is slightly different from the previous methods.
This does a plain import in quick way.
Public Sub Convert_XML_To_Excel_From_Local_Path() Dim xml_File_Path As String Dim wb As Workbook 'Load XML Data into a New Workbook - Code from Officetricks.com Application.DisplayAlerts = False xml_File_Path = ThisWorkbook.Sheets(1).Cells(2, 1) Set wb = Workbooks.OpenXML(Filename:=xml_File_Path) 'Copy Content from New workbook to current active Worksheet wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet2").Range("A1") 'Close New Workbook & Enable Alerts wb.Close False Application.DisplayAlerts = True End Sub
1.2 Advanced method to Read each node in XML file using VBA
This VBA code will import XML to Excel by reading each element or node in it, using object model.
If it is not just an plain import & you want to process each XML node in code, then this is the most preferred method.
Public Sub Convert_XML_To_Excel_Through_VBA() 'Code from Officetricks.com 'Add referece from Menu: "Tools -> References -> Microsoft XML Vn.0" Dim iRow As Integer, iCol As Integer Dim xmlDoc As MSXML2.DOMDocument, xmlRoot As MSXML2.IXMLDOMNode Dim xmlNodes As MSXML2.IXMLDOMNode, xmlData As MSXML2.IXMLDOMNode Set xmlDoc = New MSXML2.DOMDocument 'Load & Wait till complete XML Data is loaded xmlDoc.async = False xmlDoc.validateOnParse = False xmlDoc.Load ("D:\sitemap.xml") 'XML Loaded. Now Read Elements One by One into XML DOM Objects Set xmlRoot = xmlDoc.DocumentElement Set xmlNodes = xmlRoot.FirstChild 'Read XML Data and Load into Excel Sheet by each Node and Chile Node iRow = 0 For Each xmlNodes In xmlRoot.ChildNodes iRow = iRow + 1 iCol = 0 For Each xmlData In xmlNodes.ChildNodes iCol = iCol + 1 ThisWorkbook.Sheets(2).Cells(1, iCol) = xmlData.BaseName ThisWorkbook.Sheets(2).Cells(iRow, iCol) = xmlData.Text Next xmlData Next xmlNodes End Sub
Before executing the code above, just change the path of the input file in the line, xmlDoc.Load.
By this method, you will be able to import multiple XML files into Excel.
1.3 Import XML into Excel using VBA from Website
The above method is used to load data from a local file. Use the below method if you have to import XML data to Excel from a website URL directly.
Public Sub Import_XML_From_URL() 'Clear & Setup Output Sheet - Code from Officetricks.com ThisWorkbook.Sheets(2).Activate ThisWorkbook.Sheets(2).Cells.Select Selection.ClearContents 'Switch Off Alert Messages while Importing. Application.DisplayAlerts = False 'VBA Command To Import XML File from Website to Excel ThisWorkbook.XmlImport URL:="XML_File_Download_URL", ImportMap:= _ Nothing, Overwrite:=True, Destination:=ThisWorkbook.Sheets(2).Range("$A$1") Application.DisplayAlerts = True End Sub
All the methods discussed above can be used to automate the process of converting XML to Excel file from a local computer or from a Website URL.
2. How to Convert XML to Excel?
To convert XML file to Excel using menu options, follow these simple steps.
- Open a Excel workbook (xls or xlsx).
- Click ‘File’ menu & choose ‘Open’ (shortcut – ‘Alt+F+O’).
- Browse folder path & choose XML file.
- Click ‘Open’ or press ‘Enter’.
- Choose ‘As an XML table’ in popup menu & click ‘OK’.
- Click ‘OK’ when Excel notify that a schema will be created based on XML source data.
Now, the system will import XML to Excel in active worksheet.
But, wait. We do have more easy methods & even few VBA codes do perform this task.
3. Data Import XML to Excel – Menu Option
To view XML in proper readable format, you can open XML files in Excel using the “Data Import” feature as explained below.
- Click ‘Data’ menu & choose ‘From Other Sources’ (shortcut – ‘Alt+A+F+O’)
- Select “From XML Data Import”.
- Choose XML path and file in ‘Select Data Source’ window.
- Excel will notify about schema creation by itself. Click OK.
- Choose target cell address to import XML data.
- Click ‘OK’.
Usually in manual options, Excel itself will guide you to further steps to import XML to Excel. These option are built in options of Microsoft XML converter.
It is also possible to import data in XML to Excel through macro Code. We have explained 3 different methods in this article to this through VBA.
View XML Maps – Data mapping using Excel
Excel will create a schema by itself once your import XML into Excel. To view the XML maps added to the Excel workbook, follow these steps.
- Right click on imported XML table.
- Select ‘XML’ in the popup menu & choose “XML Source”.
- Sidebar on right hand side will appear with source details.
- Edit Map field details in this sidebar.
- Click on ‘XML Maps’ to view list of maps linked.
- Rename, Add, Delete, Assign map schema details in this window.
The same option is also available in Developer Tab “Document Panel” from the Excel Menu. Use this option to assign a new schema or map to the imported XML file.
Note: Similar to editing importing XML, you can create a XML file from Excel directly, by saving it in XML format, right from the ‘File’ menu ‘Save As’ Option.
Import Sitemap to Excel
Bloggers, Web Admin, SEO & Marketing people scan through sitemaps from lot of websites to improve the performance of their pages in search engine result pages. They can use the techniques in this page to import Sitemap from a Website and process all the links within that Domain.
We have completed few quick steps about Extensible Markup Language or XML to Excel converter. This file format is used to exchange data between computer applications across Internet, LAN or apps within same Computer.
This file can be opened in any Internet browsers (Firefox, Chrome, IE, Opera etc.,) or a Text editor directly to view it’s content since it is not a encoded format. But, to view it in a readable format, this article will help more.
Other Reference: Microsoft Answers on Importing XML data
By this, you can create your own Excel XML viewer. We have summarized 2 manual methods & 3 different methods using VBA. Be it on-demand process or automation, this complete guide about converting a XML file to Excel will be a handy quick reference.
6 thoughts on “VBA Macro Import – Read XML File into Excel – 5 Easy Tips”
So I get an xml file that comes through as an .xls file. After I open it I can resave it as xlsx. However, it is frequently too many rows to open under xls. Is there any way I can convert this to xlsx.
You can open the MS Excel App, Then try to open the xml file with Alt + F + O (File open) dialogue.
options 2.1 & 2.2 gives options to programmatically read the xml file content.
I am not sure what you mean by option 2.1and 2.2. I tried and there dod not seem to be any options.
To convert a xml to xlsx, you need to use the Excel VBA Macro code under this Title: “Quick VBA To Convert XML to Excel”
The first macro seems to assume I can open the file which I can’t due to the number of rows. The second one I think I am missing XML Vn.0 as it is throwing an error on MSXML2.DOMDocument. Thanks for the help.
1: Install Msxml from this page.
2: Try Second method. Read xml node by node.
Could you please send a sample xml file to me that you are facing issue?
With the second method, we can read each node from xml.
By this way, if the number of rows gets exhausted, we can write to the next sheet.