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.