Excel Spreadsheet Autofill Range (Multiple rows) via MATLAB command

Hi there!

I am working automating excel work flow and trying to get MATLAB to autofill a range in an Excel spreadsheet.

The range I am looking into is multiple rows in a column with various equations and the fuction would emulate selecting those rows in the column and autofilling the adjacent column with the equations by draging the "Plus" at the bottom right corner of the cell.

I ran into similar challanegs the memebers faced but the solution I found is unclear. Any help would be amazing.

Thank you!

Answer 1

[dpb]

Start by creating a macro that does what you want done and look at in in VBA -- I usually just paste it into the MATLAB editor as comments as my rosetta stone. Then look up the VBA doc for the methods referenced and translate from VBA syntax to COM, remembering there is no VBA compiler to translate named parameters nor autocomplete named constants -- you've got to do it all.

<Excel.range.autofill> is link to the method; there's link there to the named constants for type; you'll have to be able to set the range object from user input or program logic.

Oh...just noticed a previous <Answers/165083-autofill-range-in-excel-using-matlab> solved the problem for you already...that's one I hadn't yet needed.

[Salah]

I see, so you're saying I technically can write the macro in MATLAB and then have it via COM code execute the range.autofill function in excel. As in I can define the ranges for the cells in MATLAB even?

"Oh...just noticed a previous <Answers/165083-autofill-range-in-excel-using-matlab> solved the problem for you already...that's one I hadn't yet needed."

I saw this but confused as to how the range is autofilled especially line for "r(3)".

[dpb]

"...trying to get MATLAB to autofill a range in an Excel spreadsheet."

How but COM were you thinking to do this? Aren't you using COM already in what you've done so far? If not, how did you automate workflow so far?

"...confused as to how the range is autofilled especially line for "r(3)"."

...
r(1) = sheet.Range('I2');
r(2) = sheet.Range('J2');
r(3) = sheet.Range(sprintf('J%d', size(time, 1)));
sourceRange = sheet.get('Range',r(1),r(2));
fillRange = sheet.get('Range',r(1),r(3));    

sourceRange.AutoFill(fillRange,0)
...

The example range above is fixed -- the source range to be copied is two columns I2:J2, the range expression could have been written that way just as well as with the two explicit cells, the target range is then from that corner to the bottom -- the OP to whom @Guillaume was responding clearly had an array named time which was used to determine the size of the target arena --

>> time=seconds(randi(40,10,1));
>> sprintf('J%d', size(time, 1))
ans =
    'J10'
>> 

so r(3) is 'J10' in my example; it would be whatever the number of rows of time() were at the time the code were executed.

Then, the AutoFill method is called; its syntax is that it is called from the source range object and gives the target/fill range as the first argument with the FillType constant as the second. See the aforereferenced VBA doc for the skinny on that.

Again, create a macro that does what you want and see what it generates; its code should pretty-much duplicate what is above.

NB: the try...catch...end clause here will only let you abort; that's not a good design; if there is a problem, you need to be sure to not leave hanging an open workbook as would that code at that point since it can't get there unless it had succeeded in opening the workbook first, before trying to find the specific sheet.

[dpb]

workbook = Excel.Workbooks.Open(excelFullFileName);
sheets=workbook.Sheets;
Sheet=sheets.Item('Sheet1');
sourceRange=Sheet.Range('I2:J3');
fillRange=Sheet.Range('I2:J10');
sourceRange.AutoFill(fillRange,0)
Excel.ActiveWorkbook.Save
Excel.ActiveWorkbook.Close(0)

did, in fact, work as advertised. I started with

time in S           

0.0113222        566110  0.011322 

0.02264294        566111  0.033965 

as the content of E1:J3 where I2:I3 were the constants shown and J2:J3 were

=J1+E2
=J2+E3

The result was the continuation of the series in both columns -- numeric in I and the formula in J, just as if had selected I2:J3 in the sheet and drug the corner down.

"Piece 'o cake!!!!" it appears...although I'd not had need to try it before, I think I'll take the template and add to the Excel utilities class here.

[Salah]

This clears things out. I tried it and it worked. Thank you!