Accessing all workbooks of a Spreadsheet through COM Object

Finance and Operations/AX Developer Forum
1

Hello. I have a custom report that produces an Excel spreadsheet with between 1 and 19 tabs/worksheets - depending upon how many checkboxes the user checks on the generate form before hitting the generate button. I want to freeze the top row of each worksheet, but currently my code only freezes the top row of the first worksheet.

public void newWorksheet(String255 _worksheetName)
{
COM comWindow, comApplication;
countWorksheets++;
if (worksheets.count() >= countworksheets)
{
worksheet = worksheets.itemFromNum(countWorksheets);
}
else
{
worksheet = worksheets.add(null, null, 1);
}

worksheet.name(_worksheetName);
progress = new SysOperationProgress();
progress.setAnimation(#AviUpdate);
progress.setCaption(_worksheetName);
currentRow = 1;

comApplication = application.comObject();
comWindow = comApplication.activeWindow();
comWindow.splitRow(1);
comWindow.freezePanes(true);
}

Apparently although I call this method once for each worksheet, the active worksheet is always the same object. I tried using the code

comWindow = worksheet.comObject();

but this did not work for me, it returned an object of name ‘worksheet’ but the activeWindow() method is returning an object named ‘Window’. Is there a way to iterate through all windows of a COM object, or to find the Window from a COM Workbook?

I am using Dynamics AX 2012 , version 6.0.1108.670.

2

You’re right it works only with the active worksheet, therefore you have to activate (Activate()) the worksheet you want before getting the active window.

3

Thank you! Took a little to figure out how to make it work, but it’s working now. Here’s my working code if anyone else ever has this issue (changes are in yellow):

public void newWorksheet(String255 _worksheetName)
{
COM comWindow, comApplication, comSheet;
countWorksheets++;

if (worksheets.count() >= countworksheets)
{
worksheet = worksheets.itemFromNum(countWorksheets);
}
else
{
worksheet = worksheets.add(null, null, 1);
}

worksheet.name(_worksheetName);
progress = new SysOperationProgress();
progress.setAnimation(#AviUpdate);
progress.setCaption(_worksheetName);
currentRow = 1;

comApplication = application.comObject();
comSheet = worksheet.comObject();
comSheet.activate();
comWindow = comApplication.activeWindow();
comWindow.splitRow(1);
comWindow.freezePanes(true);
}