OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

OOo Calc AutoFill (How to?)

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
nom
OOo Enthusiast
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Mon Jan 19, 2004 2:10 pm    Post subject: OOo Calc AutoFill (How to?) Reply with quote

I was reading throught the spreedsheat API pages and all the examples are in Java!

I am trying to use StarBasic with OOcalc to specify a row range and have the first row start with a DATE and then auto increment (either by day or by week, or by month) to fill in the specified range.

I could do this using a "for" loop but I am sure that there is a way of doing it elegantly in basic. (I actually saw how to do it in Java). Can anyone point me in the right direction?

Thanks,

nom

P.S: For example I would like to fill in the range "A1:A12" starting with 01/01/04 (1st of January) and finishing with 01/12/04 (1st of December)
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Mon Jan 19, 2004 2:58 pm    Post subject: Reply with quote

Not elegant but you can use the dispatch ... I recorded this macro
Code:
sub fild
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "StringName"
args1(0).Value = "1jan"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args1())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$B$1:$B$12"  'fill range

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------------------------------------------------------------------
dim args5(5) as new com.sun.star.beans.PropertyValue
args5(0).Name = "FillDir"
args5(0).Value = "B"
args5(1).Name = "FillCmd"
args5(1).Value = "D"
args5(2).Name = "FillStep"
args5(2).Value = "1"
args5(3).Name = "FillDateCmd"
args5(3).Value = "M"  'Month ... could be day,weekday, month year
args5(4).Name = "FillStart"
args5(4).Value = "37987"  'ist jan 2004
args5(5).Name = "FillMax"
args5(5).Value = "1.70E+307"

dispatcher.executeDispatch(document, ".uno:FillSeries", "", 0, args5())


end sub
Back to top
View user's profile Send private message
nom
OOo Enthusiast
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Mon Jan 19, 2004 11:25 pm    Post subject: Reply with quote

Thanks dfrench,

Isn't there an easier way. I was reading through the API files and came across this:
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/XCellSeries.html#fillSeries

but how do I use it in Basic?

nom

If I manage to get it working I will post back.
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Tue Jan 20, 2004 10:02 am    Post subject: Reply with quote

Code:
Sub Main
oRange=ThisComponent.sheets(0).getcellrangebyposition(0,0,0,11)  'the cell range a1:a12
' fill down using the date in A1 as start point
oRange.fillseries(com.sun.star.sheet.FillDirection.TO_BOTTOM,com.sun.star.sheet.FillMode.DATE, com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH, 1,1.70E+307)      
End Sub


Yes much neater, but did require some hunting around the dev guide following your reference.
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Tue Jan 20, 2004 11:57 am    Post subject: Reply with quote

dfrench wrote:
Not elegant but you can use the dispatch ... I recorded this macro...


dfrench, with apologies, I can rewrite your macro much shorter, and easy to read. It depends on a new subroutine I introduced over in Code Snippets...
http://www.oooforum.org/forum/viewtopic.php?t=5058

Here is my rewrite...

Code:

DocumentDispatch( ThisComponent, ".uno:EnterString", "", 0,_
   Array(_
      MakePropertyValue( "StringName", "1jan" ) ) )

DocumentDispatch( ThisComponent, ".uno:GoToCell", "", 0,_
   Array(_
      MakePropertyValue( "ToPoint", "$B$1:$B$12" ) ) )  ' fill range

DocumentDispatch( ThisComponent, ".uno:FillSeries", "", 0,_
   Array(_
      MakePropertyValue( "FillDir", "B" ),_
      MakePropertyValue( "FillCmd", "D" ),_
      MakePropertyValue( "FillStep", "1" ),_
      MakePropertyValue( "FillDateCmd", "M" ),_
      MakePropertyValue( "FillStart", "37987" ),_
      MakePropertyValue( "FillMax", "1.70E+307" ) ) )

' FillDateCmd...  M =  Month ... could be day,weekday, month year
' FillStart...  37987 = 1st jan 2004

_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
nom
OOo Enthusiast
OOo Enthusiast


Joined: 17 Dec 2003
Posts: 153
Location: NSW, Australia

PostPosted: Wed Jan 21, 2004 9:54 pm    Post subject: Reply with quote

OK I tried all of your codes, but none of them worked...

Did you try your code (dfrench and Danny) before posting, or was it off head?

Also How did you find out about all these arguments for this (and their meaning)
Code:
      MakePropertyValue( "FillDir", "B" ),_
      MakePropertyValue( "FillCmd", "D" ),_
      MakePropertyValue( "FillStep", "1" ),_
      MakePropertyValue( "FillDateCmd", "M" ),_
      MakePropertyValue( "FillStart", "37987" ),_
      MakePropertyValue( "FillMax", "1.70E+307" ) ) )


thanks,

nom
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Thu Jan 22, 2004 8:36 am    Post subject: Reply with quote

I'm just following along, hoping to learn something about Calc. I rewrote dfrench's macro simply as an example of my subroutine. I did not try the original. The rewrite should be functionally identical.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Thu Jan 22, 2004 10:50 am    Post subject: Reply with quote

Both my examples were tested on my environment. There is a slight difference in the function between the dispatch and api example ... in the api example, A1 must contain a value to be used in the fill down.

The codes used in the Dispatch example are as recorded so the meaning for the values relates to the action recorded ... I asked for an increment of month so FillDateCmd = "M" ... you could geuss some of the alternatives (perhaps Y for year or record the macro to get the other values). The value 1.70E+307 is probably the largest value that can be assigned ... in practice here it only needs to be equal or greater than the largest filled date.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group