MATLAB Answers

0

With activeX server running Excel, access the cells syntax on range property

Asked by Thomas Hermelin on 7 Aug 2019
Latest activity Commented on by Guillaume
on 13 Aug 2019
Hello there,
I am trying to fill an excel sheet by running an activeX server, and I would like to access the right range like i would do in VBA, for instance :
WS.Range(Cells(1,1),Cells(10,2))
I used to use this syntax, but with letters for the column index:
e = actxserver('Excel.Application');
eW = e.Workbooks;
eF = eW.Open(result);
e.Visible = 0;
e.DisplayAlerts = false;
eS = e.ActiveWorkbook.Sheets.get('Item',1);
eS.Activate;
eActivesheetRange = get(e.Activesheet,'Range','A1:B10');
eActivesheetRange.Value = Data;
I would like to run a loop to write the right data in the right column.
(I know I can use xlswrite and so but that's not the point ;) )
Thanks !
Thomas

  1 Comment

When automating Excel/Word/etc. you should never rely on Activexxx as whatever is active may change without you knowing (e.g. a macro or the user doing something else wiith Excel/Word/etc.)
In your example code, it's also completely pointless since e.Activesheet will basically return the eS you already have unless the user or a macro actually change the active sheet.
Also, note that most times, you don't need to use get:
excel = actxserver('Excel.Application'); %invisible by default
excel.DisplayAlerts = false;
workbook = excel.Workbooks.Open(result);
worksheet = workbook.Sheets.Item(1); %no need for get
range = worksheet.Range.Item('A1:B10'); %no need to go through activesheet, or get
range.Value = data;

Sign in to comment.

2 Answers

Answer by Bob Nbob
on 7 Aug 2019

I used to do this by creating a variable ('range' for simplicity) that was defined with string concatenation. Because you want the range to vary I would suggest using sprintf.
for i = 1:26
range = sprintf('%c%i',i,i);
end
The results of the loop should be single cells A1, B2, C3, ..., Z26. You can do this for both starting and ending, and you can define integers in the string, rather then as variables. Keep in mind though that %c for 27 is not AA, so you will need to create a different string condition if you plan on having the range cover columns greater than Z.

  2 Comments

Thank you, i seams to work (but the characters from 1 to 26 return blank or nothing, is that an encoding problem ? )
The characters are not blank or nothing, they're actually control characters.
Bob is missing an offset for the character conversion, it should be:
range = sprintf('%c%i', col+'A'-1, row); %only works for row up to 26

Sign in to comment.


Answer by Guillaume
on 13 Aug 2019
Edited by Guillaume
on 13 Aug 2019

As Bob showed you can compute the address as text to pass to the Range property directly. However, that gets a bit complicated if you have more than 26 columns.
Matlab does not support default COM properties, so you have to be explicit each time you want to access the Item property that VBA hides for you. So, the VBA
rg = WS.Range("A5")
in matlab is:
rg = WS.Range.Item('A5')
With Cells it's even more complicated as matlab gets confused by COM properties with two indices. You have to use get wit Cells, so:
rg = WS.Range(Cells(1,1),Cells(10,2))
is in matlab:
rg = WS.Range.Item(get(WS, 'Cells', 1, 1), get(WS, 'Cells', 10, 2))
Computing the actual range as a char vector may be more readable than this!

  0 Comments

Sign in to comment.