Search code examples
vbscriptxlsmexcel.application

open and save a xltm file


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".


Solution

  • 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