MATLAB Answers

Yasha
0

Autofill range in excel using Matlab

Asked by Yasha
on 2 Dec 2014
Latest activity Edited by Guillaume
on 4 Dec 2014
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

Show 1 older comment
the error is on the line:
sourceRange.AutoFill(fillRange,Excel.constants.xlFillDefault)
Did you not spot the answer below?
Does it not answer your question?

Sign in to comment.

Tags

1 Answer

Answer by Guillaume
on 2 Dec 2014
Edited by Guillaume
on 4 Dec 2014
 Accepted Answer

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

sorry, i didn't spot the answer below previously. i have run the code you suggested and get the error:
Undefined variable sheet.
Error in excel_copy_2 (line 215)
r = sheet.Range('I2');
the error dialog message also pops up so im pretty sure its to do with the line
sheet = Workbook.Item(resultsheet);
just replaced
sheet = Workbook.Item(resultsheet);
with
sheet = get(Excel.Worksheets,'Item', resultsheet);
and it works a treat. thanks!
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).

Sign in to comment.