[Solved] Can't make fillAuto work

Discuss the spreadsheet application

[Solved] Can't make fillAuto work

Postby mack » Mon Dec 13, 2010 2:36 am

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

Re: can't make fillAuto work

Postby Charlie Young » Mon Dec 13, 2010 3:28 am

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

Re: Can't make fillAuto work

Postby mack » Tue Dec 14, 2010 2:25 am

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

Re: Can't make fillAuto work

Postby Charlie Young » Tue Dec 14, 2010 2:47 am

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
Attachments
FillAuto.ods
AutoFill
(8.84 KiB) Downloaded 203 times
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Can't make fillAuto work

Postby mack » Sat Dec 18, 2010 3:57 am

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

Postby mack » Sat Dec 18, 2010 4:37 am

BUT-- your macro works great on MY sheet. my head's beginning to hurt...
openoffice 3.2.1 on windows 7
mack
 
Posts: 5
Joined: Mon Dec 13, 2010 12:58 am

Re: Can't make fillAuto work

Postby Charlie Young » Sat Dec 18, 2010 7:46 am

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

Re: Can't make fillAuto work

Postby mack » Sat Dec 18, 2010 6:53 pm

Charlie, thanks for all your input- my problem is solved, and i put away the aspirin...!
openoffice 3.2.1 on windows 7
mack
 
Posts: 5
Joined: Mon Dec 13, 2010 12:58 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 12 guests