Creating multiple worksheets in Excel using X++

Question

I am currently able to create a new Excel spreadsheet, write to it and save the spreadsheet through X++.

I have a few methods within a new class, each intended to write to a separate worksheet within the workbook.

The first method creates the spreadsheet, writes the data, saves the spreadsheet, and then quits Excel just fine. But within the next method I attempt to open the saved spreadsheet, write to another worksheet (tab), save again, and quit Excel.

The second method causes the following error. Method 'saveAs' in COM object of class '_Workbook' returned error code 0x800A03EC () which means: You cannot save this workbook with the same name as another open workbook or add-in. Choose a different name, or close the other workbook or add-in before saving.

Is it possible to create and save multiple Excel worksheets within one Excel workbook in X++?

@JanB.Kjeldsen Thanks for both replies. Forgot to mention that I am working with AX 2009, Excel 2010 on Windows Server 2008 Standard, 32-bit. — AX_Dev
@JanB.Kjeldsen Been tied up working on other items as this is more of an informational piece, but I have been looking @ some of the code in the SysDataExcelCOM class used by the Data Import/Export for some guidance. I was able to programmatically delete the standard "Sheet 1, Sheet2, Sheet 3" worksheets from my Excel Workbook by studying this class. Will keep you posted... Thanks — AX_Dev

AX_Dev AX_Dev · Accepted Answer · 2013-01-03T14:18:42

Thanks again to Jan for responsing

After reviewing the SysDataExcelCOM class, there is a CreateWorkbook method which creates the additional worksheets for the Excel Data Definition export. After creating a new similar class method and also declaring my own set of defined names (#define.ExampleExcelWorksheetName('abcSheet') for the Excel Worksheets, I was then able to create the Excel Workbook with multiple worksheets. Then save (SysExcelWorkbook.saveAs) after all of the necessary sheets are created first, rather than open the saved Workboook then adding worksheets per new class method.

Within the other class methods where data is intended to write to the Worksheets, I opened the Workbook created by the new method similar to CreateWorkbook (sysExcelWorkbooks.open(yourExcelFile), then refer to each Worksheet using (sysExcelWorksheet.itemByName) to write the data to the specific Worksheet, then save.

excelApplication    = SysExcelApplication::construct();
excelWorkbooks      = excelApplication.workbooks();
excelWorkbooks.open(fileNameSave);
excelWorkbook       = excelWorkbooks.item(1);

//Add styles and fonts
excelStyles = excelWorkbook.styles();
excelStyle  = excelStyles.add("Header");
excelFont   = excelStyle.font();
excelFont.bold(true);

excelWorksheets     = excelWorkbook.worksheets();
excelWorksheet      = excelWorksheets.itemFromName(#declared name of your worksheet);

// Begin Header Row
excelWorksheet.cells().item(1,1).value("value of your choice");
excelWorksheet.cells().item(1,2).value("value of your choice");
excelWorksheet.rows().item(1).style("Header");

excelWorksheet.name("Rename your declared worksheet name or use current name here");
excelCells          = excelWorksheet.cells();
excelCells.range('A:B').numberFormat('@');

//Find you data to write to Excel Worksheet here

excelWorksheet.columns().autoFit();
excelApplication.displayAlerts(false);
excelWorkbook.saveAs(fileNameSave);
excelWorkbook.comObject().save();
excelWorkbook.saved(true);
excelApplication.quit();

Going this route worked well for me.

Creating multiple worksheets in Excel using X++

3 Answers