December 7, 2007

How to make a daily calendar with OOo

Author: Michael Crider

When I hear "mail merge," I usually think of personalizing letters and printing envelopes. However, many other projects can make use of mail merge. This year I tackled a new Christmas gift project by using mail merge in (OOo) to create a tear-off daily calendar, personalized with holidays and family events. Here's how.

I started with OOo Calc, the spreadsheet portion of the suite. In the first row I put two column labels: DATE and EVENT. Under DATE I put the starting date for the calendar: 1/1/08. I then selected the cell, clicked on the black box in the lower right corner, and dragged downward until I saw the ending date that I wanted. In the EVENT column I put any events that I wanted to show on the calendar, next to the appropriate date. Once I had everything the way I wanted, I saved the spreadsheet as a dBASE (.dbf) file. Even though OOo has a good database now, I still use dBASE files for simple mail merge operations.

To use this dBASE file in mail merge, I needed to register it with OOo. I started a new Database (File -> New -> Database), and chose to "Connect to an existing database - dBASE." I then pointed the Browse button to the directory in which I saved the dBASE file. OOo treats dBASE files as tables, with the directory being the "database." It is OK to have other file types in the directory -- they will be ignored for database operations. I asked OOo to register the database for me, but did not ask it to "Open the database for editing" or "Create tables using the table wizard." I gave the database connection a name, and was then ready to start my calendar.

The final steps were accomplished in Writer, the word processor. I wanted my calendar to be a quarter of a letter-sized sheet. To accomplish this easily, I used the Brochure printing feature of OOo. I set a User Paper format equal to a half sheet: 5.5 inches wide and 8.5 inches high. I laid out the calendar page on the top half. To put in the dates, I went to Insert -> Fields -> Other... -> Database:


I chose "Mail merge fields," and expanded the database connection and the dBASE table to see the list of fields. I clicked on DATE, then chose "User-defined" under Format and "Additional formats..." in the drop-down list. On the Number Format dialog I typed in a Format code of NNN, then clicked OK. After clicking on the Insert button on the Fields dialog, I had a grey tag in my document labeled <DATE> that would be replaced by the day name on each page, fully spelled out. I repeated this process using Format codes of MMMM to give the full month name and D to give the date number. Finally I inserted the EVENT field at the bottom with no special formatting.

With these four tags in the document, I adjusted the font style and size, centering, and other display attributes until I had the layout I wanted. Again I made sure this layout took only the top half of the page. With the cursor at the end of the text, I went back to Insert -> Fields -> Other... -> Database, and chose type "Next record." With the dBASE filename highlighted, I clicked Insert. This showed up in the document as a small gray block with no label. I then copied everything from the top half (up to but not including the Next record block) and pasted it in the bottom half, leaving enough open space in the middle to provide decent margins when the page is cut in half.

Now I was ready to generate the final calendar. I clicked on File -> Print, and answered yes to "Your document contains address database fields. Do you want to print a form letter?" At this point in any version older than 2.3, your only option is to send it to the printer. With 2.3, you have the option to send the merged data to a single document. I chose File under Output, then chose "Save as single document." After clicking OK, I was given the normal "Save as" dialog. After it finished creating the new document, I opened it and turned the computer over to my wife. She is the creative one, so she added graphics on appropriate pages and made sure everything looked nice.

I then went back to File -> Print. I clicked the Properties button, and set the Orientation to Landscape (the Paper size was already on Letter). Back in the main Print dialogue, I clicked on the Options button, and checked Brochure under Pages. When I clicked OK to print, my printer gave me four pages on a sheet, ready to cut apart and stack.

There are several ways you can bind the printed calendar. I cut a piece of stiff paneling to a little larger than the calendar sheets, drilled two holes in it, punched holes in the paper, and attached them with two bolts and nuts, and put a hanger on the back. You can also buy the glue used to bind memo pads and build a desk stand. If you have any other ideas, leave them in the comments below.


  • Office Software