Macro's in Libre Calc

users
#1

Hi Guys,

Who is the best person to speak to about macro's in calc?

cheers
Phil

#2

Hi :slight_smile:
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 :slight_smile:

#3

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

#4

Hi :slight_smile:
It sounds more like a database would do the trick.

https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Handbook
Regards from
Tom :slight_smile:

#5

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.

#6

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

#7

Hi :slight_smile:
That might be a better route!

The database route might be better if the wide-eyed-end-users were going to
enter the data directly onto a computer such as a hand-held device or
tablet or something. Sadly LIbreOffice does not yet work on hand-held
devices but AndrOO does but i'm not sure if the Base component is included.
So the weeus might have to use a desktop machine for it.

I didn't quite follow Brian's instructions but that might be because i've
got a fever and only read it hastily. Writer does sound like a good module
to use. I'm guessing that each module would have different advantages.

I had a quick stab at doing this in Calc but just on a single worksheet to
try to take advantage of the auto-increment feature but it didn't work well
when there was a gap between the dates. Using multiple sheets didn't seem
likely to work either.

I also had a quick go at creating a copy of the file, renaming the
file-ending to zip and then editing the "contents.xml". It kinda worked
with minimal info on each page but i could imagine it become hideously
turgid with even a little more information on the pages.

Regards from
Tom :slight_smile:

#8

Hi :slight_smile:
Ahh, i hadn't tried this either and this makes much more sense to me.

So it looks like there are many ways of dodging the need for a macro and
that helps keep the document flexible for the future.
Regards from
Tom :slight_smile: