OpenOffice.org Training, Tips, and Ideas

Cell formats, and using NOW() and other functions in OpenOffice Calc spreadsheets that you use in databases and mail merges

Mail merges work fine with spreadsheets as the data source. You create a spreadsheet of data, then choose File > New > Database, specify connecting to an existing database, Spreadsheet as the type, then click Next and point to the database itself.

Here are a couple things about formats, though.

Formats don't come through. So if you want your Salary field to have dollar signs, decimals, etc. you need to set the format. Likewise with date or time.

One way to do this is just set the formats when you view them. Press F4 and expand to view the query or table you want. Then just right-click on the column heading and choose Column Format.

Then in the window that appears you can select a category (Currency, Date, Number) and the the specific format you want. Click OK.

You can do the same thing in the database file itself. Open the .odb file, click the Tables or the Queries icon at the left, then double-click on the particular table (the sheet or query).

In the window that appears, right-click on the column heading, choose Column Format as before...

and in the window that appears, as before, make the choice you want. Click OK.

Now, here's a related topic. Can you use the =NOW() function in a spreadsheet and have it interpreted correctly in the database and in mail merges? (Thanks to a Colorado Springs LUG member for this idea.) The answer is Yes.

Here's a spreadsheet with =NOW() showing the absolutely current time and date.

Here's what it looks like, with nothing done to it, in the database view. It needs some tender loving formatting since it's just showing the internal numeric value. Right-click on the column heading and choose Column Format.

 

Set the format you want. The NOW() function can let you use a date or time format since it contains both.

 

And now it looks fine in the database view.

 

When I use this field in a mail merge....

Here's the output when I format the field as a date:

 

and when I format it as a time.

How to create a series, or just repeat a cell, in OpenOffice Calc spreadsheets

Never retype when you can do it quicker!

In OpenOffice.org Calc spreadsheets, as in Excel, you can click on a cell, find the little black handle in the lower right corner, and drag it up, left, right, or down, to get additional content.

 

If it's a number when you drag you'll increment by 1. If it's a value like the days of the week or names of months, it will increment as in January February March.

 

If it's a formula where you've just typed it normally without absolute  references, then drag it down through a column or row, it will repeat the formula as shown. Here's the first cell with the formula:

  

Drag down and you get similar formulas referencing the next-door cell, not the original cell.

If it's anything else it will just repeat.

If it's a number or day/week/month (not a formula) that usually increments, and you just want it to repeat, you can:

- Select the cell containing the number, as well as all the cells that you want it to repeat into, and choose Edit > Fill > down or right or whatever fits the cells you've selected

- Or you can hold down Ctrl when you drag and it'll do the same thing: repeat instead of incrementing. This works for incrementing months/days etc and numbers.

Note: for formulas, if you want them to not adjust as you drag, you use absolute references as in Excel. $A$4 instead of A4 for instance.

The OpenOffice.org Web Wizard, for mass-converting docs to HTML or PDF

I wrote this article  for TechTarget about the fabulous Web Wizard and its uses for mass PDF conversion and quick web publishing of existing documents. This is a "classic" post but it's a great feature that bears re-posting about.

Two easy setup changes everyone should make in OpenOffice.org Writer

You have a lot of control over how OpenOffice is set up. One of them is automatic formatting. I recommend turning off most of it. Here are two things to do that will make OOo work the way you want.

Choose Tools > Autocorrect, Options tab, and unmark everything from the second line on down. This will prevent, among other things, the numbering formatting that  starts when you don't expect it.

Then choose Tools > Options > Writer > Print and unmark the circled option. It will take out that annoying extra page you get sometimes and you don't know why . (It has to do with right-left pagination for books.)

Suppressing empty fields (and the lines they're on) in OpenOffice.org labels, or any mail merge document

Note: This is a repost but useful. It's important to follow the steps exactly. Everything is case sensitive. Also when you type the two "" quotes, don't put a space between them. If you have spaces in your database, this won't work. Either change the database field names, or create a query based on the table, and change the field names in the query. Then base the labels on the query.

Here it is -- suppressing a blank Address2 field in your mail merges. It's not extremely simple, but it's reasonably straightforward and it works.

Here's the situation we're addressing. Sometimes your addresses have two lines for the address part, sometimes they don't.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave
Broomfield, CO 82211

But you have to put in the <Address2> field for everybody, since it's a mail merge. The setup has to be the same.

<Firstname> <Lastname>
<Address1>
<Address2>
<City>, <State>, <Zip>

But with this approach, your addresses look like this.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave

Broomfield, CO 82211

Ick. How do you suppress that second Address2 line and the corresponding carriage return if there's no content for a particular record, for that Addres2 field?

Select the Address2 field in your mail merge document, choose Insert > Section, and create a conditionally hidden section with this formula.

databasename.tablename.fieldname EQ ""

Here are the details, using an example of labels.

1. Create the labels for mail merge as usual. File > New > Labels, select your database and tables, insert the fields, etc.

2. Choose the Synchronize Contents checkbox.

3. Click New Document.

4. Here are the labels.

5. Turn on nonprinting characters if they're not on already.

6. Select the first soft return, shown selected.

7. Press Return or Enter to replace it with a hard return.

8. Repeat, to make them all hard returns.

9. Click Synchronize to update the other labels to be the same.

10. Select the Address2 field.

11. Choose Insert > Section.

12. Name the section Suppress. Select the Hide checkbox and type the following condition. The screen shot shows the syntax.

Syntax
databasename.tablename.fieldname EQ "" � �(the last part is two double quotes together)

Example
databasewithtwoaddresslines.Table1.Address2 EQ ""

NOTE: if you are using the Thunderbird address book as a data source, you need to use square brackets if the field name includes a space (i. e.: [Address 2]=="") to hide the second line of the address if it the Address 2 field is blank.)� I would suggest in general avoiding field, table, or database names with spaces.

http://www.oooforum.org/forum/viewtopic.phtml?t=43528&highlight=

Click the screen shot to see it bigger. It shows the syntax, not an actual example.

13. Click Insert.

14. Click Synchronize.

15. Now preview the data or print the data and you'll see that it prints correctly.

15. If you need to change the section, select it in the first address and choose Format > Section. Select the one named Suppress for the master label and make changes, then click OK. Click Synchronize again in the labels.

---

---

The simple and elegant PhotoAlbum extension for OpenOffice.org Impress

Once I installed this extension, it took me about six seconds to create a presentation based on a directory of graphics, one slide for each graphic.

http://extensions.services.openoffice.org/node/419

Download the extension, don't unzip it, then choose Tools > Extension Manager as always to add it.

Then choose Tools > Addons > Create Photo Album.

Point to the directory where your graphics are. That's it. You get a presentation.

Only thing is, the randomly chosen slide transitions are a little annoying, so click the Slide Transitions item, select No Transition (or something you like), then click Apply to All Slides. And you can use that pane also to change the amount of time on each slide, or to switch it to manually going from one slide to another.

 

Creating Views and Queries in OpenOffice.org 2.0

Views and queries let you cherrypick what fields, and what data, you want to see. You can also create calculated fields to add data, and change the field names. You can then base mail merges, among other things, on what you've created.

This is an article on creating views in OpenOffice.org 2.0. Queries are pretty much the same thing with a few differences.

See also part 1 and part 2 of an article about creating databases in the database tool in OpenOffice.org 2.0, and the forms article, part 1 and part 2.

---

When in doubt, right-click.

When in doubt, right-click.

The right-click context menu gives a very nice list of many of the operations you can do with the selected item. Not everything, of course, but it's a good place to start. (Click to see a bigger version.)