CODEWITHSUNDEEP
vbscriptxlsmexcel.application
Maya
Maya

Reputation: 27

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

Upvotes: 0

Views: 3223

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200453

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

Upvotes: 1

Related Questions