Merging cells problem with actxserver
15 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Blue
el 5 de Mayo de 2023
Comentada: Cris LaPierre
el 5 de Mayo de 2023
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;
0 comentarios
Respuesta aceptada
Fangjun Jiang
el 5 de Mayo de 2023
Editada: Fangjun Jiang
el 5 de Mayo de 2023
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.
3 comentarios
Fangjun Jiang
el 5 de Mayo de 2023
Editada: Fangjun Jiang
el 5 de Mayo de 2023
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.
Más respuestas (1)
Cris LaPierre
el 5 de Mayo de 2023
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;
2 comentarios
Fangjun Jiang
el 5 de Mayo de 2023
The OP's problem was caused by the overwritten variable 'eSheet1'.
I like the way you do Merge.
Ver también
Categorías
Más información sobre Use COM Objects in MATLAB en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!