OpenOffice.org Training, Tips, and Ideas

Queries in OpenOfice.org Mail Merge: how to print only documents for the records you want, in the order you want them

I hate it when I blithely skip by fantastic built-in features.

No more skipping by.

So you've got 2000 employees, or members, or whatever. You need to send out a letter reminding people to, oh, sign up for benefits, or renew their membership, or maybe it's just a congratulatory letter for some people who have five years of service now and are going to get that sterling silver ice bucket they've always wanted. Or let's say you need to print labels for everyone but you need to do it in order by zip code or the people at the post office are going to be very annoyed.

You don't need to set up a query ahead of time in Base or in Calc. You don't need to go into Base or Calc and sort the information. You could, it would be fine, but it's more work than you need to do.

Just specify what you want, when you print.

You know how to print a mail merge letter, envelope document, or label document. You choose File > Print, click Yes in that little window that asks you about a form letter, and you get the print window.

Check out the top row of icons.

 

Sorting

Here's what you get when you click the Sort icon. Just choose to sort by a field, up or down, then others if you need to. Just like the sort in Calc.

 

Or if you just want a quick sort-by-one-field thing, click on  column of your choice, then click the A>Z or Z>A icon to sort ascending or descending.

Filtering

To use the quick autofilter, click on a particular cell in a row. One single value, like the city where one person is from. Click the AutoFilter icon; you'll get all the records that match that value.

 

Results:

 

If you want a little more control over the filter, click the Standard Filter icon. It's just like the standard one in Calc. NOTE: The AND/OR fields in the 2nd and 3rd rows aren't enabled until you select something to filter by in those rows. It looks like they're not available, but they are.

 

And results.

 

To turn off the standard filter, click the Apply Filter icon; to get the filter back on, click it again.

You can also remove any filter or sort with this icon.

When you're done, just print as usual; I recommend selecting File, and Save as Single Document. Click OK and name the output file.

This creates a perfectly ordinary Writer document with all the fields merged, one copy of your original mail merge document for each record you chose to print by. 

Then you open that Writer document, check it over a bit to be sure it's what you want, then print that as you would any other Writer document. (The illustration cuts out the bottom half of each page for space reasons; you would have an entire page for each person.)

Another way to tweak labels, and save the format

In this entry I talked about a fairly straightforward but manual way of giving your labels a little room to breathe.

In this entry, I'll go through how to use the Format tab to tweak a particular layout, then save it for re-use.

When you create labels, you of course choose File > New > Labels. You select your type and layout here, then add your content, and then click New Document.

You get something that looks like this.

Now, what if you then print and everything is too high, too low, too much to the left, etc.?

Well, you just adjust it, then save that adjustment as a specific format you can select next time.

When you're in the Labels window, click the Format tab.

Here's what all the measurements mean. I suggest starting by changing the left and top margin, then get into changing the pitch if necessary.

Horizontal pitch

The distance from the left of one label to the left of the label to the right of it. If you want to actually increase the distance between columns of labels, i.e. if labels get increasingly (or decreasingly) cut off as you go across the sheet, change this.

Vertical pitch

The distance from the top of one label to the top of the label below it. If you want to actually increase the distance between rows of labels, i.e. if labels get increasingly (or decreasingly) cut off as you go down the sheet, change this.

Width

Just the width of the actual space for the label content.

Height

Just the height of the actual space for the label content.

Left margin

The distance from the left side of your sheet of labels to where content begins. If all your labels are getting cut off on the left, adjust this.

Upper margin

The distance from the top of your sheet of labels to where content begins. If all your labels are getting cut off on the top adjust this.

Columns

The number of columns. You don't need to adjust this.

Rows

The number of rows. You don't need to adjust this.

Examples

Here's a normal sheet, next to one where I increased just the top margin. Click the image to see it larger.

Here's an example where I increased the vertical pitch by a half inch. You wouldn't want to increase it that much, but I made it big to make sure you could see the effect. Click the image to see it bigger.  Note that on the right, you only are at the 5th row while at the same place on the left, you're at the 7th row.

Once you've got the label adjustments where you want them, click Save in the Format tab. Name the label in the window that appears, and click OK.

Then when you create labels again, that saved format will be in the list.

---

---

Printing Envelopes in OpenOffice.org Writer (Repost)

I ran into Christian Einfeldt at Linuxworld, who mentioned that he had some questions about printing  envelopes. It's a good perennial topic, so I'm rereposting.

(Originally posted December 2, 2005)

I get a lot of questions about envelopes. A lot. I wrote an article for TechTarget.com about how to do envelopes in OpenOffice.org 2.0.

I included some templates there; here are the same envelope templates for OpenOffice.org 2.0.

• Envelope template, not hooked up to a data source, using frames
• Envelope template, not hooked up to a data source, not using frames

The article is long and detailed. Here are the key points.

• You need to get to know your printer and let it know to expect envelope shapes, not letter or A4 shapes.

• You also need to fiddle around for a while and figure out where--left, right, perpendicular, parallel--in the tray your printer expects envelopes. Buy a box of cheap envelopes and expect to waste a few while you experiment.

• Then make sure that your envelope document is set up to print to the envelope size your printer is expecting. Envelope 10 is good.

• You can type in your data for the envelope or use a data source. My article goes over both.
• The simplest way is to just choose File > New > Envelope, then Format > Page Size and instead of Letter or A4, choose Envelope 10 and change the orientation to landscape.  Then either with standard margin formatting or with frames, put the text where you want.
• As another way to create the envelope document, you can also choose Insert > Envelope, and use the three-tabbed window that appears.
• Print the envelopes separately from your letter or other document that goes with the envelope. It complicates things too much with the paper size that the printer expects.

Then just print the envelope. (If you're using data sources, click Yes in the dialog box that appears asking if you want to print a form letter.)

The next time you want envelopes, just use that same document you already created, and change the addresses. Either save the document in myimportantdocuments\envelopes, or if you're a template kind of person, make it a template. (File > Templates > Save, select a category and name the template, then choose File > New > Templates and Documents and pick your envelope template.

Note: Doing it in 2.0 is quite similar to how to do it in OpenOffice.org 1.1. Here are my posts how to do that; they're excerpted from my OpenOffice.org workbooks.

• OpenOffice.org 1.x Mail Merges, Envelopes, and Labels
• OpenOffice.org 1.x Plain Labels and Envelopes

Tip on Printer Setup
Some of the pain of envelopes is the printer setup. Here's a GREAT tip from Miriam:
"I just read your envelope printing tip. Instead of constantly changing and checking the printer settings, I add another instance of the printer, configure it for envelopes and name it "envelope." When I want to print an envelope I choose this printer instead of the default one. That way my settings are always the same."

Envelope Mantra
Here's the other main point I want to make sure everyone understands.

Envelopes aren't too bad once you figure out how to do it the first time. Honest.

---

---

A Simple Way to Do Labels in OpenOffice Writer: When You Just Want to Type or Paste the Content

Here's how to just print out a few labels very simply.

I follow this procedure when I ship out books. Let's say I've got five people who've ordered books directly from me through PayPal or Amazon. I've got five emails in my inbox, with five addresses. So I need to make five labels.

I describe the simple but more labor-intensive way in the following steps. Then at the end I show what I actually do that adds a little bit of one-time extra setup and makes the whole process much simpler.

1. Choose File > New > Labels.* (See note at end.)

In this window, look at the two lists in the lower right corner. Pick the right layout, usually Avery Letter, and the kind of labels you're using (the number is on the box or envelope) like 8160.

2. Click the Options tab and make sure that the Synchronize checkbox is NOT marked.

3. Click New Document.

4. You'll see the new empty label document.

5. Now just type the content you want in each box. OR copy and paste. In my example, I copy the address from my email and paste it into the box.

If you've already used up some of the labels on the physical sheet you're going to print onto, then just start pasting or typing in the next available box.

If you have trouble clicking in a box to type, click somewhere in the blank margin around the area where the boxes are, then click in the middle of the box. You'll then see a normal blinking cursor and you'll be able to type.

6. To format the text differently, you could do a few different things. Pick any of the following; I recommend b or c.

a - Just format every text box the same, manually.

b - Format the first text box how you want it, and select some of the formatted text. Then DOUBLE-CLICK the Paintbrush icon.

Select every additional text box with text to format the same way.

Then CLICK the Paintbrush icon again and you're done.

c - Format the first text box how you want it. With some formatted text selected, choose Format > Styles and Formatting. Click and hold down on the far right icon and choose Update Style. All the other boxes will be formatted the same way.

7.  Now just stick the label sheet in your printer and print. If you want more than one sheet of labels, then just print out these, then type the new addresses over the old addresses and print a second sheet. (Or save this sheet, then choose File > Save As and save the sheet under a different name, and type the second set of addresses over the old addresses.)

* NOTE: What I actually do instead of steps 1-4 and 6 is open the label template that I created. I went through this entire process, then formatted the text the way I wanted it. I formatted the text in the first label box the way I wanted it. Then I choose Format > Styles and Formatting, I clicked and held down the mouse on the far right icon, and chose Update Style, as in step 6-c above.  After that, I chose File > Templates > Save. I named the template Labels and clicked OK.

That's what I did to create the template the way I wanted it. Then instead of step 1, I choose File > New > Templates and Documents, and pick Labels from my list of templates.

---

---

Suppressing Blank Address2 Fields in OpenOffice.org Labels, Envelopes, or Other Documents, for Mail Merges

Note: 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.

---

---

Printing Exactly the Records You Want to Print for a Mail Merge in OpenOffice Labels, Envelopes, or other Documents

Used to be, it was hard to just print the records you wanted to print in a mail merge, in OpenOffice.org mail merges.

Bob  Jones   121 Ludlow
Marie Hanson  1688 Oak
Kathy Bates  88 Pearl

What if you're mad at Marie, though, as well as Jean, John, and Xavier,  and just want to print a holiday letter to Bob and to Kathy and the other 119 people in your list who you're still speaking to?

You had to either go through the HUGE and complex mail merge wizard, or just print a letter each for Marie, Jean, John, and Xavier, and throw them away.

However, in 2.something of OpenOffice.org, you can just Ctrl - Select the records to print.

1. Make your mail merge document: labels, letter, envelopes, whatever.

2. Choose File > Print and say Yes, you want to print a form letter. NEVER mark the checkbox.

3. In the window that appears, you can scroll down to view the database and table you're using at the left side. This isn't necessary but it might help you feel more organized.

4. Click on the light gray box next to the first record you want to print.  Hold down Ctrl and click on the light gray box next to the next record you want to print. And so on.

You'll see that at the left side,  Selected Records is now marked.

5. Now just select Printer or File to print as you normally would, and click OK. The normal printing window will appear if you select Printer.

---

 

OpenOffice Label Templates Article got Dugg and Delicioused

I'm a little fuzzy on how it happened, but my article, somewhat edited down, got Dugg and Delicioused bigtime last week! (The folks at WorldLabel sometimes engage me to write articles, and they re-released it. Kind of like a DVD Director's Cut or something. ;> )

Apparently it even made the front page of Delicious. Very fun.

Anyway, here's the article.

http://www.tech-recipes.com/rx/1955/making_labels_with_openoffice_org_templates

If you feel the urge to Digg it, or just FYI, it's in the Software category.
http://www.digg.com/software

---

Putting Graphics in the Background of Labels or Any Text, in OpenOffice Writer

I got a question this morning about putting graphics in the background of labels, and it's really not that obvious. Thus, I'm blogging about it.

This applies to any graphic that you want to put behind some text, i.e. a watermark but just for one page, one label, or one paragraph.

How do you do it?

Here's what you do for regular text.

Choose Insert > Picture > From File as you would normally. Select the picture you want.

The graphic appears.

Right-click and choose Wrap > In Background.

The graphic will now be behind the text.

To lighten the graphic, select it. You'll be able to use the Picture toolbar, shown. Click on Default to see the Grayscale and Watermark options, or click the Color icon on the picture toolbar to get the Color palette where you can increase brightness, decrease contrast, etc. Also try increasing transparency, another option on the Picture toolbar.

Here's what you do for labels.

Choose File > New > Labels and set up labels as you would normally.

In the Options tab, click Synchronize.

Click New Document and the labels, with a floating Synchronize button, will appear.

Insert the picture by choosing Insert > Picture > From File.

The picture appears.

Right-click on the picture and choose Wrap > In Background.

Resize and reproportion the graphic as necessary.

Reformat the text as necessary, then click Synchronize. The  labels will each have the graphic in the background.

---

Creating Two or More Pages of OpenOffice Labels Without Using a Mail Merge

Lots of people complain that in OpenOffice.org you only get one page of labels from the File > New > Labels wizard.

One  way to get around that is to not enter the data manually; use a database and create a mail merge.  Then you'll still just see one page in the label document but OpenOffice.org will print as many sheets of labels as necessary for the data.

1. Click here to create the mail merge.
2. Click here to create labels based on the mail merge.

However, if you do want to manually type two or more pages of labels, here's what I recommend. Use the labels from WorldLabel.com. They're composed of  invisible-border tables so it's easier to just create a new page, then copy the table and paste it onto the new page.

For more detail on that, see http://www.freesoftwaremagazine.com/articles/free_open_document_labels?page=0%2C2  and find the section called  Copying the first page to make additional pages. 

To see the whole article on using WorldLabel and regular label templates, click here.

 

---

Modify the Layout of Prefab OpenOffice Labels Under File > New > Labels

Note: For other label tweaking tips, click here and here.

I was just putting together the mailing labels for my books (thanks everyone!) and the 8163 label layout built into OpenOffice.org was too wide for my printer.

What to do?

Changing the margins seems like the first thing to do. But when I do that, the layout skews. The page thinks there isn't enough room for both columns of labels so the second column of labels jumps to the second page.

Then I thought, well, I'll  shrink the width of the little frame things that the text is in.

When you try to do that, though, you get the Ghostbusters symbol that indicates "no changing anything here, buddy."

However, you can change the frame so that its size and position are no longer protected.

Some of you, reading along, are saying, "Um, Solveig....there's that adjustment window in the freakin' label creation window for just such purposes. Why not use that?" That's a good one too. ;> If you haven't already typed or pasted a lot of content into your actual label document, at least.

So here are some ways to tweak your labels. These apply to the prefab ones used through the File > New > Labels window. The ones you get from WorldLabel are made from tables, not frames, so the font modification items might work but the frame stuff is irrelevant.

Tweaking Label Size in the Label Window Before You Create the Label Document

Choose File > New > Labels.

Select the correct page size and label number, such as Letter and 8163.

Click the Format tab.

The Left Margin field is what you want to increase, if the left side of your labels are getting cut off. Increase it just a tad, perhaps to .3.  Or if your margins are getting cut off on top, change the Top Margin measurement. Then click New Document to create the label, if you're ready.

The new label document will have a slightly bigger  margin to give you some breathing room. (Or it will otherwise reflect the change you made in the Format tab.)

Tweaking Already-Created Label Documents

If you've got your document and you don't want to re-create it, here's some stuff you can do. Be forewarned that it is harder to control than the previous approach.

Here's the sample I'm working with; there's content only in the top two rows just from sheer laziness on my part. ;>

Right-click on the top left frame and choose Frame.

In the Frame window, go to the Options tab and unmark the Size and Position checkboxes.

Click OK.

If you want, you can just physically drag the frames one by one. Or use this approach to modify their dimensions all at once.

Choose Format > Styles and Formatting. Be sure that Frame styles are displayed. Right-click on the Labels style and choose Modify.

Now you can do whatever works in your layout. Make the label slightly narrower, perhaps 3.75  or 3.8.

Click OK. This will update the measurements of all the frames.

Choose Format > Page. In the Page tab, set the left margin to something a tad larger, like .3. Make sure the right-hand margin is .01 or something similarly small. You want to make sure there's enough room on the left side to get all your content in on the left without it being cut off and a too-wide right margin will goof that up.

Click OK.

Your labels should have a little more space on the left now, without the content being cut off.

Alternate Approach to Indenting Text From Left
I tried this once but the frames went kaflooie. Then I tried it again and it was fine. See how it works for you.

If you haven't selected the Synchronize checkbox, use this approach to quickly reformat. Choose Format > Styles and Formatting. Be sure paragraph styles are showing, and that all of them are showing.

Right-click on Default and choose Modify.

Now you can update the Default style, used by the label text.  In the Indents and Spacing tab, specify a left indent of .2 or .3.

Click OK.  All label formatting will be updated to add more space to the left.

 

One-Step Reformatting of Label Contents
If you haven't selected the Synchronize checkbox, use this approach to quickly reformat: make all your label content 17 point red Arial Bold or whatever you want.

Choose Format > Styles and Formatting. Be sure paragraph styles are showing, and that all of them are showing.

Right-click on Default and choose Modify.

Now you can update the Default style, used by the label text.  In the Font tab you can of course change the font.

Click OK.  All label formatting will be updated.

---

Getting Data Back Out of OpenOffice Base

Click here.

Note: Having clearly labeled import and export features for Base has been proposed and you can vote for it by clicking one of the following links. Here's how to make things work until the features are implemented and put into the next build.

Vote for adding a wizard to import data into Base
http://qa.openoffice.org/issues/show_bug.cgi?id=51904

Utility to export CSV from Base
http://wiki.services.openoffice.org/wiki/CSV_export

---

Here's the related article on the cleverly disguised import function, for getting data from a spreadsheet, Access, or another database into an OpenOffice Base database.
http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1222186,00.html

---

New OpenOffice Base Database Workbook

I've finished my Base workbook!

Click here to see the TOC.

For the rest of the year it's at a Special Introductory Price of just $15. Feel free to provide suggestions or corrections for what to add to it. I'm not a DBA so would appreciate information on what additional tasks would be most useful with the tools in Base.

---

A Design Contest! Enhancing the OpenOffice.org Template Coffers

The deadline is October 31st! Still plenty of time to enter!

It's another contest! Anyone with OpenOffice.org and a dream (that's all of us, potentially) can enter.

It's the Worldlabel-sponsored OpenOffice.org Design Competition.

" The OpenOffice.org documentation project has announced a Template & Clipart Contest.

The total prize money is to the tune of $5000 sponsored by WorldLabel, which has long been a strong champion of OpenOffice.org.

The goal is to increase the trove of templates and clipart. The current collection of template samples leaves a huge gap that needs to be filled. Step up and fill that gap. Winners will also have the option of including their winning entries in the OpenOffice.org installation sets available from the site.

If you are interested, visit the Documentation Project for more information. Everyone is eligible and everyone is encouraged to participate. The contest officially starts this week and ends 31
October 2006. Winning entries will be judged by a panel of three and winners will be announced."

Enter! I'm definitely going to do it. It's fun, plus with more templates, OpenOffice.org will be even more appealing for those still with Microsoft Office.

---

How to Get the "Do You Want to Print a Form Letter?" Message Back for OpenOffice Mail Merges

If you've done mail merges before, you've seen this message.

 

What you should do, every time, is to click Yes and leave everything else alone. Then the mail merge will print with the contents of your data source.

What would make sense is to mark the Do Not Show Warning Again checkbox and click Yes, thinking that every time thereafter you'll be able to print the mailmerge correctly, with the contents of your data source, just like you did this time, but without that pesky message popping up.

You'd be logical, but you'd also be wrong because of the wacky design of the program. If you mark the checkmark, then from that day forward you will print, instead of a mail merge, a list of fields like <Firstname> and <Lastname>.

So:
How do you get that dialog box to come back so you can print a mail merge correctly?

Answer: Here's how it's supposed to work. People say this works. I can't find a file with the relevant flag in it on my machine but if it works for you, great. It should work.

<< Reader David Beroff offers some fine suggestions after his successful implementation, which I have updated the instructions with.>>

1. Close OpenOffice.org.

2. Find the Writer.xcu file. It's in one of these locations.

~/.ooo-2.0-pre/user/registry/data/org/openoffice/Office/Writer.xcu

Documents and Settings\[users]\Application Data\OpenOffice.org2\  user\registry\data\org\openoffice\Office\Writer.xcu

3. Make a backup copy of it. Just copy the file in your file manager and paste it somewhere else.

4. Open it with an Ascii editor (i.e Notepad or 1stPage or some such program.)

5. Look for <prop oor:name="AskForMerge">  and set the value to true. To do this, look for "AskForMerge=False"  and type "True" where it says "False."

6. Save the file.

Now you'll get the message popping up again when you print a mail merge. Leave the checkbox alone and click Yes, and you're golden.

 

---

Manually Controlling What's In Your Labels, in OpenOffice Writer (Repost)

(First posted February 2006)

I received this question from Chris in relation to the blog on labels.

“Is there a way to tell OpenOffice to save the document in such a way as that it essentially "exports" it to another OpenOffice document that isn't attached to the data source, so that you can hand-edit the labels? The only way I found to do it was to save it as a Word document and then re-save _that_ as a standard OOo text document. Is there a more direct way?

Thanks,

Chris”

Good question. There are a few different things you can do.

Change the Label Display So You Can See the Data, Then Edit

I don't know of a way to separate the label data from the data source in a sensible way. However, you can just change the content.

Create the labels, connected to the data source, like you normally do. Don't click the Synchronize option in the third tab.

Choose View > Data Sources.

Click the Data to Fields icon in the toolbar above the data sources.

You'll see content in the labels instead of field placeholders.

Now just edit each field normally. Change Bob to Gretchen, Fargo to Kalispell, etc, just by typing.

When you print, you'll get a conglomeration of the database data and what you typed.

Change the Data Source, Not the Labels

If you want to edit the labels, edit where the information comes from, if that's an option. If your data source is a spreadsheet, just edit the contents of the spreadsheet. Be sure that the .odb database file, and any label documents accessing the database, are all closed when you edit the data itself.

Just Make Labels That You Type the Content Into

This approach is entirely manual, but you can copy and paste into the labels. If you want control over what' s in there, just make blank labels and type.

Choose File > New > Labels. Do everything pretty much the same way, i.e. pick the right label type. But don't put any data or fields in the big empty data box in the first tab, and in the third tab don't click Synchronize. Click New Document. (Click this image to see a bigger version if you want.)

Then just type whatever you want in any of the frames.

Quickly Go From One Label to Another

The labels are in frames, which is a bit of a pain. To quickly go from one to another using the keyboard, press Esc Tab  Enter. (Intuitive, huh?) You can also choose Tools > Customize, click Keyboard, and set up a control key for going between frames.

 

---

Tags

OpenOffice.org    open office    OpenOffice.org training    open office training   open source   open source training   Office 12  Linux   Ubuntu   education   Microsoft Office  labels  mail merge    printing  

---

Mail Merge OpenOffice Labels, OpenOffice.org 2.0 (Repost)

Here's how to do labels.

First, get your data in a spreadsheet, text file, address book, and create a database. You can do this by choosing File > New > Database. 

This post contains instructions for spreadsheets.

This post contains instructions in step 2 for spreadsheets or text files.

Then, once you've got the database set up in OpenOffice.org, you're ready to go.

1. Choose File > New > Labels. (To do Envelopes, open an OpenOffice.org Writer document, and choose Insert > Envelope.)

2. In the Labels tab of the Labels window, select the database  you created in the Database dropdown list. You're looking for the name of the database you created by choosing File > New > Database, not the spreadheet, addressbook, or text file containing the data.

(Click the picture to see a larger image.)

3. Select a table from the Table list. This will be Sheet1 or whatever the sheet name is, if you are using a spreadsheet to hold your data.

4. Select the first field you want to use from the Database Field list.

5. Click the arrow next to it to insert it in the Label Text field.

Type a space after the field and you can add the next field, such as LastName.

6. Use the Database Field list to insert any other fields you need. If you want fields on the second line, click after the last character of the last field you inserted, in the big text box, and just press Enter. If you need to change the arrangement later in the created label document, you can.

7. In the Brand dropdown list, select Avery Letter Size if you're not using A4.

8. In the Type dropdown list, scroll through the billions of labels. Select the type of label you're using, 8160 Inkjet Address is a good one but just use whatever is on the envelope of labels.

9. This step and step 10 , and step 15, are optional but recommended. Click the Options tab.

10. Select the Synchronize Contents option IF you want to apply formatting, like a different font or colors or adding graphics, and make those changes apply to all of your labels.

11.Click the Setup button next to the printer display.

12. Select the printer you want to print to.

13. In the printer options (this will vary according to your operating system) specify the appropriate paper feed or tray. For now, select Manual Feed.

14. Click the New Document button at the bottom of the window. The labels will appear. This is how it's supposed to look. You won't see the data, you'll see the field names like first name. When you print, the correct data will appear.

15. OPTIONAL: IF YOU SELECTED THE SYNCHRONIZE CONTENTS CHECKBOX

If you need to make changes, like adding spaces, rearranging fields, or changing formatting, do so in the upper left label. Make the text an interesting font, or make it the size you need. You can also right-click on the border of the upper right label, choose Frame, go to the Border tab, and give it a background color.

Then click Synchronize to apply those changes to all labels.

16. Choose File > Print. A message will ask if you want to print a form letter. Click Yes.

17. If you want to print labels for only certain records, you can select them in the scrolling list of records. Select one, press Ctrl, select the next, and so on. Or you can select a range of records like 1-20.

18. If you want to just print all the labels, choose All.

19. When you're ready, just click OK. You'll be prompted again to choose your printer. Print normally.

Note: If you have any problems printing, check your printer setup using your operating system setup tools.

Note: You can save the label document and just go back to it again when you need to use it again.

---

---

OpenOffice User's Guide: Now With Bar Code Information for Envelopes

The User's Guide from the OpenOffice.org community now has information for putting bar codes on envelopes! It's all about getting the right POSTNET font, I understand.

OpenOffice Pan-Galactic Post: Posts on Charts, PDF, Spreadsheets, Templates, Drawings, OpenOffice Training, Toolbars, and Much More

I've been having a problem that some of you might sympathize with--getting posts to show up in Technorati. So as a cheater, I've created this post that links to a bunch of posts that I don't think have been showing up. Not all of them like links to current discussions or issues, just the ones I think are  important that have been missed.

So I'm sorry this isn't new content, but perhaps somewhere in the last six months there's something useful that Technorati didn't let you see the first time around.

Templates, Writer, general setup and toolbars

• Creating and using OpenOffice templates, including converting Microsoft Word templates for use in OpenOffice
• Doing calculations in tables
• The styles posts on what they are, how to get styles from one document to another, how to create and update them, etc.
• The much-discussed topic of centering text vertically in a Writer document
• Starting page numbering on page 2 and related topics (relates to page styles)
• Tables of contents
• Searching and replacing for carriage returns (paragraph breaks), tabs, and other items, using regular expressions (regex)
• Creating your own toolbar
• Setting up OpenOffice under Tools > Options and Tools > Autocorrect. This is how to turn off word completion, stop automatic formatting, set defaults, increase icon size, and much more.

Calc spreadsheets and charts

• Calc spreadsheet data entry shortcuts
• Dragging a cell in a spreadsheet (see the end of the post)
• Charts in Calc and Writer; how to click in just the right spot and modify exactly what you want.
• Opening a CSV text file in Calc so that it opens as a spreadsheet. Useful if you export files from databases of other formats and want to create them as spreadsheets or databases in OpenOffice.
• Controlling what people can put into spreadsheets with error messages, online help for the cell, ranges of permitted data, etc.

Draw, Diagrams, Impress presentations

• Using the 3D shapes in Draw (I love this stuff)
• Using OpenOffice FontWork, which is kind of like WordArt
• Creating your own arrows and other line ends for UML diagrams and other purposes
• Using the OpenOffice Gallery to store graphics; this too is useful for UML diagrams
• Printing Impress presentation handouts

Web publishing and PDF

• What I did with the web tools, creating colors, image maps, etc. to redo my getopenoffice.org StarOffice and OpenOffice Training page
• PDF, with linked articles on PDF presentations, and using hyperlinked PDFs. That post is particularly interesting since you can generate PDFs from linked OpenOffice.org documents, and the links carry over to the PDF.
• Using the wonderful Web Wizard (that's the techtarget article, here's the blog link) for creating web sites from existing OpenOffice, Microsoft, and graphics documents. You can also use it for  PDF batch convert.

Mail merge, labels, envelopes, and databases

• The very very powerful Mail Merge Wizard.
• The less powerful but far easier Secret Mail Merge that you need to add to your toolbar.
• Creating OpenOffice.org databases from spreadsheets, text files, and your address book. Even Access databases are easy.
• A bunch of articles for TechTarget.com on creating databases from scratch in Base,  creating forms, doing reports, views and joins between tables , and all the fun new stuff in the Openoffice Base database module.
• How to do labels the regular way, and manually.

Openoffice training, change management, and general discussions

• My thoughts on training and its benefits.
• My suggestions for transitioning to OpenOffice.org, including the change management aspects.
• Why OpenDocument Format is important (parental advisory, contains references to tube tops).
• Switching to OpenOffice.org, or any open source product, means you can spend the budget leftover on something important. Education, firefighters--anything that's more important than using Microsoft Office.
• Office 2007 could be a big retraining job. Think about whether you'd rather spend money and time upgrading to Office 2007, or just time on switching to OpenOffice.

---

Manually Controlling What's In Your Labels, in OpenOffice Writer

I received this question from Chris in relation to the blog on labels.

“Is there a way to tell OpenOffice to save the document in such a way as that it essentially "exports" it to another OpenOffice document that isn't attached to the data source, so that you can hand-edit the labels? The only way I found to do it was to save it as a Word document and then re-save _that_ as a standard OOo text document. Is there a more direct way?

Thanks,

Chris”

Good question. There are a few different things you can do.

Change the Label Display So You Can See the Data, Then Edit

I don't know of a way to separate the label data from the data source in a sensible way. However, you can just change the content.

Create the labels, connected to the data source, like you normally do. Don't click the Synchronize option in the third tab.

Choose View > Data Sources.

Click the Data to Fields icon in the toolbar above the data sources.

You'll see content in the labels instead of field placeholders.

Now just edit each field normally. Change Bob to Gretchen, Fargo to Kalispell, etc, just by typing.

When you print, you'll get a conglomeration of the database data and what you typed.

Change the Data Source, Not the Labels

If you want to edit the labels, edit where the information comes from, if that's an option. If your data source is a spreadsheet, just edit the contents of the spreadsheet. Be sure that the .odb database file, and any label documents accessing the database, are all closed when you edit the data itself.

Just Make Labels That You Type the Content Into

This approach is entirely manual, but you can copy and paste into the labels. If you want control over what' s in there, just make blank labels and type.

Choose File > New > Labels. Do everything pretty much the same way, i.e. pick the right label type. But don't put any data or fields in the big empty data box in the first tab, and in the third tab don't click Synchronize. Click New Document. (Click this image to see a bigger version if you want.)

Then just type whatever you want in any of the frames.

Quickly Go From One Label to Another

The labels are in frames, which is a bit of a pain. To quickly go from one to another using the keyboard, press Esc Tab  Enter. (Intuitive, huh?) You can also choose Tools > Customize, click Keyboard, and set up a control key for going between frames.

 

---

Tags

OpenOffice.org    open office    OpenOffice.org training    open office training   open source   open source training   Office 12  Linux   Ubuntu   education   Microsoft Office  labels  mail merge    printing  

Mail Merge Labels in OpenOffice 2.0

This post is similar to the big post here on how to do version 1.1 labels, etc. It works pretty much the same way. However, this is a nice little standalone HTML post on how to do labels in 2.0 so you might prefer this.

First, create a database. You can do this by choosing File > New > Database. I've written an article on how to do this if you just want to connect to a spreadsheet, text file, or address book as your data source. I recommend spreadsheets.

Once you've got the database set up in OpenOffice.org, you're ready to go.

 

1. Choose File > New > Labels. (To do Envelopes, open an OpenOffice.org Writer document, and choose Insert > Envelope.)
2. In the Labels tab of the Labels window, select the data source  you created and want to use from the Database dropdown list. (Click the picture to see a larger image.)

3. Select a table, Sheet1 if you're working with a spreadsheet, from the Table list.
4. Select the first field you want to use from the Database Field list.

5. Click the arrow next to it to insert it in the text field. Type a space after the field and you can add the next field, such as LastName.

6. Use the Database Field list to insert any other fields you need. If you want fields on the second line, click after the last character of the last field you inserted, in the big text box, and just press Enter. If you need to change the arrangement later in the created label document, you can.
7. In the Brand dropdown list, select Avery Letter Size.
8. In the Type dropdown list, scroll through the billions of labels. Select the type of label you're using, 8160 Inkjet Address. The label type is on the label sheet or box of labels.

9. Click the Options tab.
10. Select the Synchronize Contents option.

11.Click the Setup button next to the printer display.
12. Select the printer you want to print to.
13. In the printer options (this will vary according to your operating system) specify the appropriate paper feed or tray. For now, select Manual Feed.
14. Click the New Document button at the bottom of the window. The labels will appear.

If you need to make changes, like adding spaces, rearranging fields, or changing formatting, do so in the upper left label, then click Synchronize to apply those changes to all labels.
Choose File > Print.
A message will ask if you want to print a form letter. Click Yes.
15. If you want to print labels for only certain records, you can select them in the scrolling list of records. Select one, press Ctrl, select the next, and so on. Or you can select a range of records like 1-20.

16. If you want to just print all the labels, choose All.
17. When you're ready, just click OK.

If you have any problems printing, check your printer setup using your operating system setup tools.

Note: You can save the label document and just go back to it again when you need to use it again.

Note: Feel free to use the formatting features. Make the text an interesting font, or make it the size you need. You can also right-click on the border of the upper right label, choose Frame, go to the Border tab, and give it a background color.

 

---

Tags

OpenOffice.org    open office    OpenOffice.org training    open office training   open source   open source training   Office 12  Linux   Ubuntu   education   Microsoft Office  labels  mail merge    printing   

What I'm Learning This Week in OpenOffice Training in Largo, Florida

I always learn something when I teach a StarOffice or OpenOffice class. In Green River, Wyoming two weeks ago, I learned how to redock the slide pane in Impress/Draw (for some versions of the software). Click here to see the post.

I also learned that cleaning the dash on a Subaru can knock your hazards on, and they don't flash so it looks like they're your regular lights. I also learned that Green River Imports is a very fine car-fixing establishment that doesn't laugh at you when you bring in your car for something like that.

This week I'm in Largo, Florida at the City of Largo. The weather is gorgeous. I'll post pictures when I get back. I'm teaching an all-Linux set of classes this week--notable, the City of Largo completely skipped Microsoft Office. Never had it or Windows running for the city. Fabulous.

A few of the things I learned from student questions, or things that students discovered and told me about,  include:

- You can just click somewhere in a table, then choose Table > AutoFormat, to apply an autoformat--you don't need to select the whole table.

- When you're putting spacing between columns under Format > Page, Columns, or for a section too, it won't accept a space between the columns if you type the measurement. You have to use the arrows to put in the spacing.

- I should have seen this earlier but in mail merge documents, you need to specify the printer before you print. In labels or envelopes just click the Options tab and specify the printer. Or in any created document, choose File > Print Properties and select the printer. Then choose File> Print, click Yes to print a form letter, be sure the Printer radio button is selected, and click OK. Used to be, you got a chance after that window to specify the printer. Not anymore.

- The mail merge wizard in 2.0.1 now does let you do manual editing when you set up the contents and layout of the address block, so it's now less annoying.

 

- To sort a data source for mail merges, so that for instance everything prints in order by zip code, here's what you do. Choose File > Open to open the data source, the .odb file. Double-click the table you're basing the mail merge on. In the editing window, click the column you want to sort by, and click one of the sort icons, Sort Ascending or Sort Descending. Then do your mail merge. If you need to then change the sort for another mail merge, just repeat these steps.

- I've been reminded that it's not a bad idea to just export your Impress slides to PDF and run your presentation in Adobe Acrobat. (In OOo, File > Export as PDF.) For anyone looking for an OpenOffice.org Impress viewer application, PDF might be all you need. Granted, it won't run your cool effects but unless you're presenting on something where you need to demonstrate motion, you don't technically need custom animation or slide transitions.

You can of course also export to HTML and to Flash; I haven't played enough with Flash to know how the transitions come over, if at all.

- Hyperlinks transfer over to PDF!!! This did not happen in 1.x. However, in 2.0 OpenOffice.org, any hyperlink in a document such as a hyperlinked table of contents or any link period, transfers to the PDF when you choose File > Export as PDF. This is GREAT.  To create a plain old hyperlink, select some text to link and click the Hyperlink icon on the top toolbar.

- In labels, to go from one frame to another on the keyboard (or to go from one label to another, period), press Esc  Tab  Enter. Intuitive, huh? You can also choose Tools > Customize, click Keyboard, and set up a control key for it.

- Cuban food is yummy. I had a pork with black beans and rice thing the other day for lunch. However, when you carry the bag of Cuban food the wrong way, the black beans leak out of the container, out of the paper bag, and splash all over your light beige suit, when you don't have time to drive back to the hotel to change.  However,  my polyester suit from the fine folks at Target was easy to dab down and get clean with just soap and water. So that was a relief. (I mostly love Target, but will take this opportunity to plead with them to carry tall sizes in pants and jeans.)

 

---

Tags: OpenOffice.org OpenOffice.org, Open Office, StarOffice, StarOffice training, openoffice.org training, openoffice training, education, open source, city government, Largo

Printing Envelopes in OpenOffice.org 2.0

I don't usually link to other content but I get a lot of questions about envelopes. A lot. I wrote an article for TechTarget.com about how to do envelopes in OpenOffice.org 2.0, and for this post I'm just going to link to it.  : )

I included some templates there; here are the same envelope templates for OpenOffice.org 2.0.

• Envelope template, not hooked up to a data source, using frames
• Envelope template, not hooked up to a data source, not using frames

The article is long and detailed. Here's the key point. You need to get to know your printer and let it know to expect envelope shapes, not letter or A4 shapes. You also need to fiddle around for a while and figure out where--left, right, perpendicular, parallel--in the tray your printer expects envelopes. Buy a box of cheap envelopes and expect to waste a few while you experiment.

Then make sure that your envelope document is set up to print to the envelope size your printer is expecting. Envelope 10 is good.

You can type in your data for the envelope or use a data source. My article goes over both.

The simplest way is to just choose File > New > Envelope, then Format > Page Size and instead of Letter or A4, choose Envelope 10 and change the orientation to landscape.  Then either with standard margin formatting or with frames, put the text where you want.

You can also choose Insert > Envelope, and use the three-tabbed window that appears.

Then just print the envelope. (If you're using data sources, click Yes in the dialog box that appears.)

The next time you want envelopes, just use that same document you already created, and change the addresses. Either save the document in myimportantdocuments\envelopes, or if you're a template kind of person, make it a template.

Note: Doing it in 2.0 is quite similar to how to do it in OpenOffice.org 1.1. Here are my posts how to do that; they're excerpted from my OpenOffice.org workbooks.

• OpenOffice.org 1.x Mail Merges, Envelopes, and Labels
• OpenOffice.org 1.x Plain Labels and Envelopes

Tip on Printer Setup
Some of the pain of envelopes is the printer setup. Here's a GREAT tip from Miriam:
"I just read your envelope printing tip. Instead of constantly changing and checking the printer settings, I add another instance of the printer, configure it for envelopes and name it "envelope." When I want to print an envelope I choose this printer instead of the default one. That way my settings are always the same."

Envelope Mantra
Here's the other main point I want to make sure everyone understands.

Envelopes aren't too bad once you figure out how to do it the first time. Honest.

I hope the article helps.

OpenOffice.org 1.x Mail Merges, Envelopes, and Labels: From the GetOpenOffice.org Workbook

Note: You can get label templates from several online resources including the WorldLabel templates here.

OpenOffice.org 2.0 is out, but I wanted to make sure that everyone out there in OpenOffice.org 1.x land knows how to do mail merges. Everyone isn't going to be converting to OpenOffice.org 2.0 the day it comes out,  so I hope this information will be useful for quite a few people for a while.

Mail merges are things like sending a holiday newsletter to everyone in your address book, with the same content but addressed to Dear Bob, Dear Mary, and so on. Mail merges also apply to envelopes and labels, since you might also want to print one envelope with the address for Bob on Main Street, Mary on Elm, and so on.

The procedure for doing mail merges isn't hard...it's just not obvious.

I'm posting the mail merge procedures from my GetOpenOffice.org Comprehensive Writer workbook, as well as the lab files that you need to do the exercises.

Enjoy! It's easy when you know how.

Click here get the procedure PDF document. Download mailmerge.pdf

Click here to get the spreadsheetM document you can use as the data for the mail merge. Download spreadsheetM.sxc

OpenOffice.org 1.x Plain Labels and Envelopes: From the GetOpenOffice.org Workbook

 

I wanted to make sure that everyone out there in OpenOffice.org 1.x land knows how to do plain old envelopes and mail merges. (These are just based on text and graphics you specify rather than coming from a database.) Everyone isn't going to be converting to OpenOffice.org 2.0 the day it comes out, so I hope this information will be useful for quite a few people for a while.

Envelopes and labels actually work very similarly in OpenOffice.org 2.0, so don't have any reluctance to learn this approach if it's your first time with labels.

There are no lab files necessary for these exercises; you create all documents from scratch.

Download envelopesandlabels.pdf