Monday, April 9, 2012

Excel 2007 -- Dynamically save a macro enabled workbook


#keyword Excel 2007 SaveAs file format

#story, when trying to dynamically save an Excel 2007 document -- that was after injecting a code into the ThisWorkbook module, the following error appeared:
The following features cannot be saved in macro-free workbooks 
-VB project 
 To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list
To continue saving as a macro-free workbook, click Yes. 

so, here's what I do when trying to save it

// example
var oExcel = new ActiveXObject("Excel.Application");
var oBook = oExcel.Workbooks.open(oThis.sExcelFile);
oBook.SaveAs('file.xlsm', 52);
oBook.Close(false);

//the file formats are:

".xlsx" = 51
".xlsm" = 52
".xlsx" = 51
".xls"  = 56
".xlsb" = 50