Autofill range in excel using Matlab

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

[Guillaume]

When reporting an error message thrown by matlab, always report the entire error message including the part that shows the line where the error occurs.

Which line is at fault?

[Yasha]

the error is on the line:

sourceRange.AutoFill(fillRange,Excel.constants.xlFillDefault)

[Yasha]

any ideas?

[Guillaume]

Did you not spot the answer below?

Does it not answer your question?

Answer 1

[Guillaume]

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

[Yasha]

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);

[Yasha]

just replaced

sheet = Workbook.Item(resultsheet);

with

sheet = get(Excel.Worksheets,'Item', resultsheet);

and it works a treat. thanks!

[Guillaume]

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