[Solved] Can't make fillAuto work

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

[Charlie Young]

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.

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

[Charlie Young]

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


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

[mack]

BUT-- your macro works great on MY sheet. my head's beginning to hurt...

[Charlie Young]

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.

[mack]

Charlie, thanks for all your input- my problem is solved, and i put away the aspirin...!