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

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

[Guillaume]

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;

Answer 1

[Guillaume]

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!

[Muhammad Hanif]

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'.

Answer 2

[Bob Thompson]

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.

[Thomas Hermelin]

Thank you, i seams to work (but the characters from 1 to 26 return blank or nothing, is that an encoding problem ? )

[Guillaume]

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]

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