How can I automatically collapse grouped columns in a spreadsheet?

How can I automatically collapse grouped columns in a spreadsheet which is created using Excel's COM Automation Server?

The excel spreadsheet generated using the following code snippet:

excelObj = actxserver('Excel.Application');
wb = excelObj.workbooks.Add;
worksheets = excelObj.Sheets;

for ii = 1 : worksheets.Count - 1
    worksheets.Item(1).Delete;
end

sheetObject = wb.Sheets.Item(1);
sheetObject.Range('A1:A2').Value = {'1';'2'};
sheetObject.Range('A1:AX50').Value = num2cell(magic(50));
sheetObject.Range('G:K').Columns.Group;

wb.SaveAs([pwd,'\example.xlsx']);
wb.Close(false);
excelObj.Quit;
delete(excelObj);

creates a group including the columns G, H, I, J, K but this group is shown as expanded when opening the generated excel spreadsheet.

Answer 1

[MathWorks Support Team]

This question is not purely related to MATLAB as it involves the Excel's COM Automation Server. As such, the underlying commands are specific to Excel and therefore the following website can be used for documentation,

https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model

Nevertheless, the answer to this question can be found in the following documentation link,

https://www.mrexcel.com/board/threads/vba-to-collapse-groups.89127/#post-437549

where the following command needs to be used,

>> sheetObject.get('Columns','G').ShowDetail = false;

Note that the first column of the group 'G:K' is used in order to programmatically collapse the group of the generated excel spreadsheet.

Moreover, it is recommended to use the command,

>> sheetObject.get('Columns','G:K').Group;

instead of,

>> sheetObject.Range('G:K').Columns.Group;

to group a particular set of columns.

The suggested replacement to the aforementioned code snippet is then the following one:

sheetObject = wb.Sheets.Item(1);
sheetObject.Range('A1:A2').Value = {'1';'2'};
sheetObject.Range('A1:AX50').Value = num2cell(magic(50));
sheetObject.get('Columns','G:K').Group;
sheetObject.get('Columns','G').ShowDetail = false;