With activeX server running Excel, access the cells syntax on range property
50 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
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 Commento
Guillaume
il 13 Ago 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;
Risposta accettata
Guillaume
il 13 Ago 2019
Modificato: Guillaume
il 13 Ago 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 Commento
Muhammad Hanif
il 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'.
Più risposte (1)
Bob Thompson
il 7 Ago 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 Commenti
Guillaume
il 13 Ago 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
il 4 Dic 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
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!