MATLAB Answers

Merge and Center in Excel

6 views (last 30 days)
Adam Evenoff
Adam Evenoff on 15 Dec 2019
Answered: Guillaume on 15 Dec 2019
I am using the code below. It works for 'Title1', however for 'Title2' it always merges cells E1:F1 instead of D1:E1 that i specified no matter the different approches I try
e = actxserver('Excel.Application');
% Add a workbook.
eWorkbook = e.Workbooks.Open(dest);
e.Visible = 1;
% Make the first sheet active.
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item',1);
eSheet1.Activate
% Merge Cells for Title1
eSheet1 = eSheet1.get('Range', 'B1:C1');
eSheet1.MergeCells = 1;
eSheet1.Value = 'Title1';
eSheet1.Font.ColorIndex = 3;
eSheet1.Font.Bold = 1;
eSheet1.HorizontalAlignment = -4108;
% Merge Cells for Title2
eSheet1 = eSheet1.get('Range', 'D1:E1');
eSheet1.MergeCells = 1;
eSheet1.Value = 'Title2';
eSheet1.Font.ColorIndex = 3;
eSheet1.Font.Bold = 1;
eSheet1.HorizontalAlignment = -4108;

  0 Comments

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 15 Dec 2019
That's because of this line:
eSheet1 = eSheet1.get('Range', 'B1:C1');
which replace the former value of esheet1 which use to be a sheet by a range reference.
Typically with matlab, you don't have to use the get method to access excel objects. I would also recommend that you don't rely on ActiveAnything, there's no point in using ActiveWorkbook when you already have a reference to the workbook so why not use it:
e = actxserver('Excel.Application');
% Add a workbook.
eWorkbook = e.Workbooks.Open(dest);
e.Visible = 1; %optional
%get 1st sheet of workbook
eSheet1 = eWorkbook.Sheets.Item(1); %No need to activate it
% Merge Cells for Title1
rg = eSheet1.Range('B1:C1');
rg.MergeCells = true;
rg.Value = 'Title1';
rg.Font.ColorIndex = 3;
rg.Font.Bold = 1;
rg.HorizontalAlignment = -4108;
% Merge Cells for Title2
rg = eSheet1.Range('D1:E1');
rg.MergeCells = true;
rg.Value = 'Title2';
rg.Font.ColorIndex = 3;
rg.Font.Bold = 1;
rg.HorizontalAlignment = -4108;

  0 Comments

Sign in to comment.

More Answers (0)

Sign in to answer this question.