I've used some code in VBScript to open a .xlsm file and save that file. Now I want to do the same thing as a .xltm file. I've tried to open the xltm file with script, it works fine. While saving that file, it refers the default location and default extension. I need to save the newly opened file with the extension ".xlsm" in the specified location. I don't know how to proceed. Please help me to solve this.
Set objExcel = CreateObject("Excel.Application")
Set WBTestFile = objExcel.Workbooks.Open(WScript.Arguments(0))'SourceFile
WBTestFile.save
WBTestFile.close
objExcel.Workbooks.Open(WScript.Arguments(0))
Here, I am passing the filename (with path) as an argument. I need to open the newly saved ".xlsm" file in the last statement. Argument: "c:\test\book1.xltm", my newly created file want to be saved in the location "C:\test\" with the extension "xlsm".
The Save
method saves the file as-is. To save it in a different format you need to use the SaveAs
method with the correct file format argument (in this case a macro-enabled Open XML template):
filename = WScript.Arguments(0)
templatename = Replace(filename, ".xlsm", ".xltm")
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(filename)
wb.SaveAs templatename, 53 'save as template
wb.Close
xl.Quit
To create a new workbook from an existing template you need to use the Add
method with the path to the template as argument and then save the file as a macro-enabled Open XML workbook:
template = WScript.Arguments(0)
filename = Replace(template, ".xltm", ".xlsm")
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Add(template)
wb.SaveAs filename, 52 'save as workbook
wb.Close
xl.Quit