Autofill range in excel using Matlab
17 views (last 30 days)
Show older comments
Hi guys,
I have a formula in an excel cell. I am trying to open the excel file from matlab and autofill the column (equivalent to double clicking the bottom right hand corner of the cell in excel). I have tried to set up a com.excel.application but have run into some problems, "I get the error No appropriate method, property, or field constants for class COM.Excel_Application."
My code is
Excel = actxserver('Excel.Application');
ResultFile = [excelFilePath_2 excelFileName2 '.xlsx'];
Workbook = invoke(Excel.Workbooks,'Open', ResultFile);
resultsheet = 'strain';
try
sheet = get(Excel.Worksheets,'Item', resultsheet);
invoke(sheet, 'Activate');
catch
% If the Excel Sheet ‘ExperimentSheet’ is not found, throw an error message
errordlg([resultsheet 'not found']);
end
r(1) = Excel.ActiveSheet.Range('I2');
r(2) = Excel.ActiveSheet.Range('J2');
r(3) = Excel.ActiveSheet.Range(['J' int2str(size(time,1))]);
sourceRange = Excel.ActiveSheet.get('Range',r(1),r(2));
fillRange=Excel.ActiveSheet.get('Range',r(1),r(3));
sourceRange.AutoFill(fillRange,Excel.constants.xlFillDefault)
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel
Is there any other method to do what I am trying to achieve? Thanks
4 Comments
Accepted Answer
Guillaume
on 2 Dec 2014
Edited: Guillaume
on 4 Dec 2014
Most likely the error comes from this line:
sourceRange.AutoFill(fillRange,Excel.constants.xlFillDefault)
There's no namespace called Excel.constants. In general matlab struggles with COM enumerations, so you're better off using the numerical values of the constants. See here for the autofill constants. However, since you're using the default, you could just omit it.
sourceRange.AutoFill(fillRange, 0); %0 is xlFillDefault
sourceRange.AutoFill(fillRange); %does the same
----
Side note: rather than working on ActiveSheet / ActiveWorkbook, I would use the sheet / workbook reference you've previously obtained. I would also use sprintf instead of numeric conversion and string concatenation:
Excel = actxserver('Excel.Application');
ResultFile = fullfile(excelFilePath_2, sprintf('%s.xlsx', excelFileName2));
Workbook = Excel.Workbooks.Open(ResultFile); %or use invoke on older matlab
resultsheet = 'strain';
try
sheet = Workbook.Worksheets.Item(resultsheet); %or use get on older matlab
%no need to activate sheet if you use its reference
catch
% If the Excel Sheet ‘ExperimentSheet’ is not found, throw an error message
errordlg([resultsheet 'not found']);
end
r = sheet.Range('I2');
r(2) = sheet.Range('J2');
r(3) = sheet.Range(sprintf(J%d', size(time, 1))); %much clearer with sprintf
sourceRange = sheet.get('Range',r(1),r(2));
fillRange = sheet.get('Range',r(1),r(3));
sourceRange.AutoFill(fillRange,0)
Workbook.Save
Excel.Quit
clear Excel %this also does Excel.delete
3 Comments
Guillaume
on 4 Dec 2014
Yes, sorry, it should have read
sheet = Workbook.Worksheets.Item(resultsheet); % or get(Workbook.Worksheets, 'Item', resultsheet);
I would use the worksheets collection of Workbook rather than the one of Excel (in case the same worksheet name is found in more than one workbook).
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!