[Solved] Can't make fillAuto work

Discuss the spreadsheet application
Post Reply
mack
Posts: 5
Joined: Mon Dec 13, 2010 12:58 am

[Solved] Can't make fillAuto work

Post 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.
Last edited by mack on Sat Dec 18, 2010 6:47 pm, edited 1 time in total.
openoffice 3.2.1 on windows 7
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: can't make fillAuto work

Post 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.
Apache OpenOffice 4.1.1
Windows XP
mack
Posts: 5
Joined: Mon Dec 13, 2010 12:58 am

Re: Can't make fillAuto work

Post 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.
openoffice 3.2.1 on windows 7
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Can't make fillAuto work

Post 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

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
mack
Posts: 5
Joined: Mon Dec 13, 2010 12:58 am

Re: Can't make fillAuto work

Post 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.
openoffice 3.2.1 on windows 7
mack
Posts: 5
Joined: Mon Dec 13, 2010 12:58 am

Re: Can't make fillAuto work

Post by mack »

BUT-- your macro works great on MY sheet. my head's beginning to hurt...
openoffice 3.2.1 on windows 7
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Can't make fillAuto work

Post 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. ;)
Apache OpenOffice 4.1.1
Windows XP
mack
Posts: 5
Joined: Mon Dec 13, 2010 12:58 am

Re: Can't make fillAuto work

Post by mack »

Charlie, thanks for all your input- my problem is solved, and i put away the aspirin...!
openoffice 3.2.1 on windows 7
Post Reply