How to selectExcel columns through Matlab activeX control?

10 views (last 30 days)
In Excel VBA I can create groups by using:
Columns(3).Resize(, 4).Group
This will make columns 3 through 7 into a group. In Matlab I have:
XL = actxserver('excel.application');
XL.DisplayAlerts = 0;
XL.Visible = 0;
XLWB = XL.Workbooks;
WB = Add(XLWB);
WS = WB.Worksheets;
AS = WB.ActiveSheet;
... bunch of stuff added to the sheet
At this point I want to group some columns. I have all the following and all have failed.
AS.Columns(3).Select
WS.Columns(3).Select
WS.Range.Columns(3).Select
What's the proper way to do this?

Accepted Answer

Matt
Matt on 22 May 2015
So the best way I found to do this was to access the columns through the Range of the ActiveSheet.
command = sprintf('%s:%s', str1, str2);
AS.Range(command).Group;
I build str1 and str2 by converting the column number into an Excel label like "AA" for 27 and "BA" for 53.

More Answers (1)

Guillaume
Guillaume on 22 May 2015
Edited: Guillaume on 22 May 2015
I don't have time to test if that's the issue (particularly since you've not said what the failure is), but with matlab sometimes you can use the dot notation, and sometimes you can't because matlab fails to find the method. In these cases you have to use invoke.
Does this work?
invoke(As.Columns(3), 'Select');
edit: Actually, isn't the issue with your indexing of the range returned by Columns. With Matlab, you can't directly use brackets to index into collections. You have to use the Item property to get individual elements:
As.Columns.Item(3).Select
  1 Comment
Matt
Matt on 22 May 2015
AS.Columns.Item(3).Select allows me to select the column. However I can not resize the selection to group it. I have tried this.
AS.Columns.Item(3:7).Select
The error I got was Invoke Error, Type mismatch
I also tried this
AS.Columns.Item(3).Resize(0,4).Select
The error here is Subscript indices must either be real positive integers or logicals.
And also:
AS.Columns.Item(3).Resize(,4).Select
|
Error: Expression or statement is incorrect--possibly unbalanced (, {, or [.
AS.Columns.Item(3).Resize('',4).Select
Index exceeds matrix dimensions.
AS.Columns.Item(3).Resize([],4).Select
Index exceeds matrix dimensions.

Sign in to comment.

Products

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!