With activeX server running Excel, access the cells syntax on range property
조회 수: 81 (최근 30일)
이전 댓글 표시
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
Guillaume
2019년 8월 13일
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;
채택된 답변
Guillaume
2019년 8월 13일
편집: Guillaume
2019년 8월 13일
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
Muhammad Hanif
2021년 3월 14일
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'.
추가 답변 (1개)
Bob Thompson
2019년 8월 7일
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
Guillaume
2019년 8월 13일
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
2023년 12월 4일
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
참고 항목
카테고리
Help Center 및 File Exchange에서 Spreadsheets에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!