Excel Export

jpeet

Forums

I am working on creating my scripts to audit multiple systems at once. I am currently using Excel to perform this task but Corporate policy is directing me to move to IDEA. I have most of the scripting done but I am wanting to export multiple database files to one Excel file. I know this is possible if I were statified with .XLSX but I need it to be .XLSM. Do you know if this is possible?
Thanks

Brian Element Sun, 08/09/2015 - 09:35

Hi jpeet,

The way you will have to do this is create your excel spreadsheets directly through IDEAScript, you won't be able to save it through the IDEA option as like you said it only does .XLSX.  The line of code to change this is the SaveAs function for Excel.  So the line would be:

obook.SaveAs Filename:= sWorkIngDir, FileFormat:=52, CreateBackup:=False

Where oBook is an excel workbook objcect, Filename is the location and the name of the file to be stored, the FileFormat tells IDEA what format to save it as, 52 is xlOpenXMLWorkbookMacroEnabled and usually for .XLSX I use 51 for xlOpenXMLWorkbook.

Hope that helpso out, if not let me know and I can give you a more fuller example.