Re: [libreoffice-users] Macros [or perhaps not] in Libre Calc

At 23:24 09/06/2014 +0100, Philip Ward wrote:
> basically what i'm after is... A cleaning/HSE Check sheet, that gets 
> printed out, and the staff fill it out, ie tick the boxes, say 
> what's 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.

So you want a 365-page document with identical text on each page 
except that each page has somewhere on it a sequential date?

> i didn't 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.

You wouldn't in any case have to do that.
o Create Sheet 1 with the first date.
o Create Sheet 2 calculating its date from Sheet1.
o Copy Sheet 2 to a new third sheet.
o Select sheets 2 and 3 and copy them to the end position. You now 
have five sheets.
o Select sheets 2 to 5 and copy them to the end position. You now 
have nine sheets.
o Repeat this copying process a further six times. You now have 513 
sheets - more than a year's worth; delete the excess.

> 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 that's copied all the rest of the year, 
> but that's about where my knowledge on libre finishes (having 
> brought most of what i knew from excel), ...

Since what you want on each page is presumably text, perhaps set in 
boxes and so on, surely the whole project is better done in a text 
(Writer) document - where you can easily create documents of 366 
pages. Writer tables are very flexible and will enable you to set out 
the text as you wish.

But how to create your varying date? Well, Writer provides 
"variables" to help you. I'm no expert in these, but I've just 
learned enough in perhaps fifteen minutes to be able to create a 
365-page document with identical dummy text on each page but also 
including text running from "1 Jan 2014" to "31 Dec 2014".

o With the cursor at the beginning of the first page, go to Insert | 
Fields > | Other... | Variables. Choose "Set variable", and give your 
variable a name ("vdate"?) and an appropriate value. I wanted to 
start at 31 Dec 2013, but I couldn't see how to enter this directly 
as a date. With the help of Calc, I quickly found the value I needed 
was 41639. Tick Invisible and click Insert.

o With the cursor where you want your date to appear, repeat the 
above, again choosing "Set variable", selecting your variable name, 
but then inserting the appropriate expression - vdate+1 - in the 
Value box. Before clicking Insert, select an appropriate format (date 
formats are hidden behind "Additional formats...") and ensure 
Invisible is *not* ticked. You now see your first date: 1 Jan 2014.

o Copy the material to a second page, carefully avoiding copying the 
invisible starting value. The copied date magically becomes 2 Jan 2014.

o Copy pages repeatedly as for the spreadsheet case, always omitting 
the first page, so as again to avoid the invisible starting value. 
After a total of ten copying actions you will have 513 separately 
dated pages - nearly seventeen months' worth. Delete the excess.

I trust this helps.

Brian Barker


--
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