Auto Save As Excel to XML

[rockstar]

Hi All...Can somebody share VB Script to automate save as function in Excel. Basically, I have a dynamic Excel File which has to be saved as corresponding XML file. I have the corresponding XML file created, so my dynamic Excel has to be saved as XML over existing XML file. Another thing, my dynamic Excel file is an XML mapped spreadsheet, in case that matters in guiding me to the right script. Thanks.

 

[Ropes4u]

Sub Macro1()
'
' Macro1 Macro
ActiveWorkbook.SaveAs Filename:= _
"C:\Book1.xml", FileFormat:= _
xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

You will have to change the file name and location, only works with the active workbook.

 

[rockstar]

Hi Ropes...Thanks for the reply. What exaclty you mean by active workbook? Do I have to open the excel file to run the macro? Sorry if I have not mentioned in my post, I'm basically looking for a way to save my dynamic spreadsheet as XML without manual intervention. My dynamic excel file is referred to another spreadsheet which is a Crystal reports output in Excel Format. So whenever new Crystal reports output is saved as Excel, my dynamic excel sheets is updated and this is where it has to be saved on existing XML file. Then that refreshed data would reflect in my dashboard which is my ultimate goal. Appreciate if you could confirm about above macro, if it would serve my purpose, if not please share script that would serve my purpose. Thank you.

 

[Ropes4u]

I am in class all week but will look at this when I get out if you have not gotten and answer by then..

 

[rockstar]

I would greatly appreciate if you can spend some time on this. Thanks again.