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.
[Solved] Can't make fillAuto work
[Solved] Can't make fillAuto work
Last edited by mack on Sat Dec 18, 2010 6:47 pm, edited 1 time in total.
openoffice 3.2.1 on windows 7
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: can't make fillAuto work
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.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.
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.
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: Can't make fillAuto work
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.
openoffice 3.2.1 on windows 7
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Can't make fillAuto work
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.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.
Code: Select all
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
- Attachments
-
- FillAuto.ods
- AutoFill
- (8.84 KiB) Downloaded 283 times
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: Can't make fillAuto work
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.
openoffice 3.2.1 on windows 7
Re: Can't make fillAuto work
BUT-- your macro works great on MY sheet. my head's beginning to hurt...
openoffice 3.2.1 on windows 7
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Can't make fillAuto work
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 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.
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: Can't make fillAuto work
Charlie, thanks for all your input- my problem is solved, and i put away the aspirin...!
openoffice 3.2.1 on windows 7