Merging cells problem with actxserver

I have an issue with using actxserver to merge cells in Excel. In the script below I expect to merge the cells B1:C1, D1:E1, F1:G1 and H1:I1 but Excel has instead merged the cells B1:C1, E1:F1, J1:K1 and Q1:R1. I dont get it. How can I get the expected result ?

% Create an Excel object.
e = actxserver('Excel.Application');

% Add a new workbook.
eWorkbook = e.Workbooks.Add;
e.Visible = 1;

% Make the first sheet active.
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item', 1);
eSheet1.Activate

% Merge some cells
eSheet1 = eSheet1.get('Range', 'B1:C1');
eSheet1.MergeCells = 1;

eSheet1 = eSheet1.get('Range', 'D1:E1');
eSheet1.MergeCells = 1;

eSheet1 = eSheet1.get('Range', 'F1:G1');
eSheet1.MergeCells = 1;

eSheet1 = eSheet1.get('Range', 'H1:I1');
eSheet1.MergeCells = 1;

% Save and close file
SaveAs(eWorkbook, [pwd, '\test_merge.xlsx']);
eWorkbook.Saved = 1;
Close(eWorkbook);

% Quit the Excel program and delete the server object.
e.Quit;
e.delete;
clear e;

Answer 1

[Fangjun Jiang]

The merge of D1:E1 was impacted by the merge of B1:C1?

Try the reverse order, F1:G1, then D1:E1, then B1:C1.

Also, variable "eSheet1" is over-written. It was Sheet but then was Range. Definitely problem there.

[Blue]

Hi. I inverted to order of the merges above and now the cells H1:I1, M1:N1 and P1:R1 are merged

[Fangjun Jiang]

It was the over-written of "eSheet1" issue. I've tried. Once you rename the

eSheet1 = eSheet1.get('Range', 'B1:C1'); to

eRange = eSheet1.get('Range', 'B1:C1');

eRange.MergeCells = 1

then, problem solved.

[Blue]

Thats right! Thank you

Answer 2

[Cris LaPierre]

To me, it appears that get('Range') is using relative rather than absolute reference. When you merge B1 and C1, B1 is treated as A1, and ('Range','D1:E1') is calculated as if B1 is the top left corner. This is more obvious if you reverse the order of merging, which is what I first tried as a fix.

I'm pretty sure this is because you overwrite eSheet1 every time to select a new range.

I would write your code like this, which I believe produces the desired result.

% Create an Excel object.
e = actxserver('Excel.Application');

% Add a new workbook.
eWorkbook = e.Workbooks.Add;
e.Visible = 1;

% Make the first sheet active.
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item', 1);
eSheet1.Activate

% Merge some cells
eSheet1.Range('B1:C1').Merge;
eSheet1.Range('D1:E1').Merge;
eSheet1.Range('F1:G1').Merge;
eSheet1.Range('H1:I1').Merge;

% Save and close file
SaveAs(eWorkbook, [pwd, '\test_merge.xlsx']);
eWorkbook.Saved = 1;
Close(eWorkbook);

% Quit the Excel program and delete the server object.
e.Quit;
e.delete;
clear e;

[Fangjun Jiang]

The OP's problem was caused by the overwritten variable 'eSheet1'.

I like the way you do Merge.

[Cris LaPierre]

Didn't refresh the page before posting.

Thanks