We can use VBA to convert and Save the Excel File as XML file. This example macro will help us to know how to convert Excel Worksheet into a XML file format. This can be used in Excel 2003,2007,2010,2013.

vba code to convert excel to XML- Syntax

Here is the example Excel VBA Syntax to convert the Excel to XML format.

Workbook.SaveAs fileName:="filepath to save the csv file", FileFormat:=xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False

VBA code to Convert and Save the Excel to XML- Example

Here is the example macro to convert the Excel worksheet to XML file.

'vba code to convert excel to xml
Sub vba_code_to_convert_excel_to_xml()
Set wb = Workbooks.Open("C:temptestwb.xlsx")
wb.SaveAs fileName:="C:temptestX.xml", FileFormat:= _
        xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

This macro will open an existing Excel workbook from the C drive and Convert the file into XML and Save the file with .xml extension in the specified Folder. We are using Workbook Open method to open a file. SaveAs method to Save the file into destination folder. This example will be help full, if you wan to convert all excel files in a directory into XML (xlXMLSpreadsheet format) file.

VBA code to Convert and Save the Excel to XML- Instructions

Please follow the below step by step instructions to test this Example VBA Macro codes:

  • Step 1: Open a New Excel workbook
  • Step 2: Press Alt+F11 – This will open the VBA Editor (alternatively, you can open it from Developer Tab in Excel Ribbon)
  • Step 3: Insert a code module from then insert menu of the VBE
  • Step 4: Copy the above code and paste in the code module which have inserted in the above step
  • Step 5: Change the Workbook name in the code as per your example folder and also change the destination file path as per your requirement
  • Step 6: Now press F5 to execute the code or F8 to debug the Macro to check the macro
120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Browse All Templates
Excel VBA Project Management Templates

All-in-One Pack
120+ Project Management
Premium Templates
View Details

Essential Pack
50+ Project Management
Premium Templates
View Details
50+ Excel
Project Management
Templates Pack
View Details
50+ PowerPoint
Project Management
Templates Pack
View Details
25+ MS Word
Project Management
Templates Pack
View Details
Ultimate Project Management Template
View Details
Ultimate Resource Management Template
View Details
Project Portfolio Management Templates
View Details
By Last Updated: January 19, 2015Categories: VBATags:

Share This Story, Choose Your Platform!

5 Comments

  1. nanette.willis@nch.com August 17, 2017 at 2:23 AM - Reply

    When converting from XLSM to XML are the macros included in the XML?

  2. PNRao August 17, 2017 at 3:29 PM - Reply

    No, XML file can not store Macros.

  3. Dilip Shah May 24, 2020 at 2:58 PM - Reply

    I think you should first apply your steps first at your end and test how it works. It is not able to generate xml file as suggested.

  4. venu June 1, 2020 at 1:39 PM - Reply

    how to set nodes instead of data

  5. Chris P October 20, 2020 at 10:03 PM - Reply

    You need to have an existing workbook

Leave A Comment