Re: [libreoffice-users] Macro's in Libre Calc

Be certain to only do what you really need to do using a macro, and then 
let things such as formulas do the rest of the work.

It sounds like you want to have a Calc document with 366 sheets (so that 
it will work with leap years) with the same sheet on each page. Well, 
the sheet is the same except for the date.

I might argue that you could simply leave the date empty and let that be 
filled in, but, lets ignore that for a second.

As a test, I created a Calc document. On Sheet 1, I entered the text 
"Date" into A1. I entered the date "01/01/14" in cell B1.
I created Sheet2 and in cell A1, I entered the formula: "=Sheet1.A1". In 
cell B1, I entered the formula: "=Sheet1.B1 + 1".
I then copied cells A1:B1, created a new sheet (Sheet3), and then I 
pasted those two cells into location A1:B1 on sheet3.

At this point, Cell A1 has the same text on each sheet, and, cell B1 has 
the dates 1/1/14, 1/2/14, and 1/3/14. Now, if I updated cell Sheet1.B1 
with a new date, then B1 updates in all the other sheets.

Is there any particular reason that you must use a new sheet for each 
printed page? Might you be able to create all the pages on the first 
sheet such that they are all on the first sheet? You would place the 
second page far enough down that it would then print on the second page 
and use formulas to refer to the previous "page / date", and then do a 
copy and paste of multiple pages in one shot. Admittedly, this will use 
a bunch of rows (around 18000 is my guess), but that is likely faster 
than created a macro.

If you do opt to write a macro....

Listing 403 in OOME_3_0.odt demonstrates inserting sheets. The code 
would probably look something like:

Dim i As Integer
For i = 1 to 365
  ThisComponent.Sheets.insertNewByName("Sheet" & CStr(i + 1), i)
Next

OK, now you have 366 sheets. You probably want to copy the content from 
sheet (i-1) to sheet i as you go. If you raelly want to transfer that 
content, you can do something like get the transferable content (Listing 
439 has an example), but, to do that, you would need to set the desired 
sheets to be active, which just feels annoying and tricky (but it can be 
done). Probably easier to:

Manually set Sheet 1 as you want.
Build Sheet 2 by hand using formulas to refer to Sheet 1. Write the 
macro to insert the sheets after 2. If you only want to copy formulas, 
then use getFormulaArray on sheet 2, then use setFormulaArray for the 
rest of the sheets... And I need to go to sleep now.

On 06/09/2014 06:24 PM, Philip Ward wrote:
> Hi Tom/all
>
> sorry for delay in getting back, and cheers for the info, ill take a look
> tomoz night and the link, that much on the go at the moment...
>
> basically what im after is...
>
> A cleaning/HSE Check sheet, that gets printed out, and the staff fill it
> out, ie tick the boxes, say whats gone of in that day etc.
>
> i have laid out a sheet, over 2 pages, and the only thing that i want to
> basically do is print out 365 (or obviously a leap year 366) page document,
> and automatically add a new date to the printed sheet for that.
>
> i didnt want to copy and paste the same thing 365 times, for 4 or 5 times,
> as there are different parts of the business, ie Cafe/Bar/Icecream kiosk
> etc, which all need their individual style/setup.
>
> i was looking for a quick way to copy the sheet, add a new sheet, paste it,
> then do the same thing over and over again. On the second sheet i know i
> can make a date = Previous sheet/Cell and +1 to it, and this would be the
> one thats copied all the rest of the year, but thats about where my
> knowledge on libre finishes (having brought most of what i knew from
> excel), but have never done any macro's of this sorts before, well part
> from gimp to re-size/touch up pictures, and even then found that i could
> have done it quicker manually!
>
> i just would prefer not to copy and paste something a potential 1825
> times!! my control and V key probably woudlnt like me very much
>
> Cheers
> Phil
>
>
> On 8 June 2014 22:00, Tom Davies <tomcecf@gmail.com> wrote:
>
> > Hi :)
> > Andrew Pitonyak's guide is pretty good and might be helpful
> >
> > https://wiki.documentfoundation.org/Documentation/Other_Documentation_and_Resources#Programmers
> >
> > Really it is best to just ask the question here and let this team build-up
> > the best answer by trying out different things.
> > Good luck and regards fro m
> > Tom :)
> >
> >
> >
> >
> > On 8 June 2014 19:23, Philip Ward <skeggy78@gmail.com> wrote:
> >
> > > Hi Guys,
> > >
> > > Who is the best person to speak to about macro's in calc?
> > >
> > > cheers
> > > Phil
> > >
> > > --
> > > To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> > > Problems?
> > > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> > > List archive: http://listarchives.libreoffice.org/global/users/
> > > All messages sent to this list will be publicly archived and cannot be
> > > deleted

--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
Info:  http://www.pitonyak.org/oo.php


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted