VBA Code Bank – Convert Excel to XML

We can use VBA to convert and Save the Excel File as XML file. This can be used in Excel 2003,2007,2010,2013.

Example to illustrate the concept:

use the following 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

'vba code to convert excel to xml
Sub vba_code_to_convert_excel_to_xml()
Set wb = Workbooks.Open("C:\temp\testwb.xlsx")
wb.SaveAs fileName:="C:\temp\testX.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 a .xml extension in the specified Folder. We are using Workbook Open method to open a file. SaveAs method to Save the file into the destination folder. This example will be helpful if you want to convert all excel files in a directory into an 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 has 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

Check out instructor-led extensive Excel VBA Course here.

Happy Excelling
Team Excelgoodies
www.Excelgoodies.com

Leave a Reply

Your email address will not be published. Required fields are marked *