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

86 views (last 30 days)
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
Guillaume
Guillaume on 13 Aug 2019
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.

Accepted Answer

Guillaume
Guillaume on 13 Aug 2019
Edited: 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!
  1 Comment
Muhammad Hanif
Muhammad Hanif on 14 Mar 2021
Hi, excuse me, I tried your code above but I got the below error :
No method 'Range' with matching signature found for class 'Interface.000208D8_0000_0000_C000_000000000046'.

Sign in to comment.

More Answers (1)

Bob Thompson
Bob Thompson 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.
  3 Comments
Guillaume
Guillaume on 13 Aug 2019
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
Ginny
Ginny on 4 Dec 2023
If you want to include letters past Z:
if col > 26 && col < 52
col2 = col - 26;
range = sprintf('%c%c%i','A',col2+'A'-1,row);
end

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!