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
Set wb = Workbooks.Open("C:\temp\testwb.xlsx")
wb.SaveAs fileName:="C:\temp\testX.xml", FileFormat:= _
xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False
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