Page 1 of 1

[Solved] Can't make fillAuto work

PostPosted: Mon Dec 13, 2010 2:36 am
by mack
i'm trying to write Basic code that makes fillAuto work like fillhandle works on a sheet.

i'm trying to write the Basic equivalent of the following Java code i found on the net:

xSeries = getCellSeries(xSheet, "A7:G9");
xSeries.fillAuto(com.sun.star.sheet.FillDirection.TO_RIGHT, 2);

i put a formula in column B32 that correctly sums up column B.
now i'm trying to write Basic code that uses fillAuto to drag the formula to the 5 cells to the right:

osheet = ThisComponent.Sheets(0)
com.sun.star.sheet.xcellseries(osheet,"b32:b32").fillAuto(TO_RIGHT,5)

but i get error "method xcellseries not found".
i'm trying to learn how the OO0 object model works- i looked at the reference material,
but i don't see what i'm doing wrong.

thanks, and any insight and/or working Basic examples of the use of fillAuto would be appreciated.

Re: can't make fillAuto work

PostPosted: Mon Dec 13, 2010 3:28 am
by Charlie Young
mack wrote:i'm trying to write Basic code that makes fillAuto work like fillhandle works on a sheet.

i'm trying to write the Basic equivalent of the following Java code i found on the net:

xSeries = getCellSeries(xSheet, "A7:G9");
xSeries.fillAuto(com.sun.star.sheet.FillDirection.TO_RIGHT, 2);

i put a formula in column B32 that correctly sums up column B.
now i'm trying to write Basic code that uses fillAuto to drag the formula to the 5 cells to the right:

osheet = ThisComponent.Sheets(0)
com.sun.star.sheet.xcellseries(osheet,"b32:b32").fillAuto(TO_RIGHT,5)

but i get error "method xcellseries not found".
i'm trying to learn how the OO0 object model works- i looked at the reference material,
but i don't see what i'm doing wrong.

thanks, and any insight and/or working Basic examples of the use of fillAuto would be appreciated.


xCellSeries isn't the range to be "dragged," it is the interface containing the fillAuto method. The second parameter of fillAuto, your 5, isn't the number of cells to be filled, it is the number of cells used to specify the fill series.

I think you want

oSheet.getCellRangebyName("B32:F32").fillAuto(com.sun.star.sheet.FillDirection.TO_RIGHT,1)

Which will take what's in B32 (1 cell), and fill it into the 5 cells in B32:F32.

Re: Can't make fillAuto work

PostPosted: Tue Dec 14, 2010 2:25 am
by mack
thanks for responding. i tried your suggestion, and didn't get an error message, but nothing happened on the sheet. i will keep working on this.

Re: Can't make fillAuto work

PostPosted: Tue Dec 14, 2010 2:47 am
by Charlie Young
mack wrote:thanks for responding. i tried your suggestion, and didn't get an error message, but nothing happened on the sheet. i will keep working on this.


The attached spreadsheet has random numbers in Sheet1.A1:F10, and the formula =SUM(A1:A10) in A11. Running Main calls AutoFill, which fills the SUM formula into B11:F11.

Code: Select all   Expand viewCollapse view
Sub Main
   AutoFill(ThisComponent.Sheets(0).getCellRangeByName("A11:F11"),1)
End Sub

Sub AutoFill(FromCell As Object, NumberofCells as Long)
   FromCell.fillAuto(com.sun.star.sheet.FillDirection.TO_RIGHT,NumberofCells)
End Sub

Re: Can't make fillAuto work

PostPosted: Sat Dec 18, 2010 3:57 am
by mack
thank you very much for helping me. i loaded your sheet and ran your macro, and got "Argument is not optional." error on the 1 statement in your AutoFill subroutine. i didn't change your sheet or your code. Thanks again for any additional thoughts.

Re: Can't make fillAuto work

PostPosted: Sat Dec 18, 2010 4:37 am
by mack
BUT-- your macro works great on MY sheet. my head's beginning to hurt...

Re: Can't make fillAuto work

PostPosted: Sat Dec 18, 2010 7:46 am
by Charlie Young
mack wrote:thank you very much for helping me. i loaded your sheet and ran your macro, and got "Argument is not optional." error on the 1 statement in your AutoFill subroutine. i didn't change your sheet or your code. Thanks again for any additional thoughts.


The "Argument is not optional" happens because AutoFill has parameters, That's why it is called by running Main, which specifies them. I just wanted to show the basic setup. Your headache should go away once you're clear on that point. ;)

Re: Can't make fillAuto work

PostPosted: Sat Dec 18, 2010 6:53 pm
by mack
Charlie, thanks for all your input- my problem is solved, and i put away the aspirin...!