Producing Address labels

Writing a book, Automating Document Production - Discuss your special needs here

Producing Address labels

Postby rjpring » Sat Dec 11, 2010 12:51 pm

I have a list of postal addresses as a spreadsheet in MY Data Sources as an .ODS file in OpenOfficeCalc.
How can I produce address labels from this. I used to use MailMerge with XP and Windows Office. If I use Tools/mailmerge with Opn Office WriterI cannot get to the file required.


OpenOffice 3.2 on Windows7
Posts: 2
Joined: Tue Nov 30, 2010 5:12 pm

Re: Producing Address labels

Postby thomasjk » Sat Dec 11, 2010 5:00 pm

See this excellent article for some help ... office_org.
Tom K.
Windows 10 Home version 1803 17134.165
Posts: 4413
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Producing Address labels

Postby John_Ha » Wed Jan 12, 2011 8:14 pm

I posted this elsewhere a few years ago...

Now I know more, I would say that of course you can use .ods files instead of xls, and I think the problems with my page format (A4/A6) were probably because I saved the page format as A6 instead of A4. However, the post was viewed neary 3,500 times, so I guess people found it useful.

After many happy hours of experimentation and searching I have finally managed to get my Christmas card labels printed off by the following method. The help is indeed misleading, not least because it never tells you the philosophy of what you are doing and also because it assumes that all the pre-requisites are already done. It's like trying to learn a foreign language from a dictionary - you cannot because a dictionary only tells you how each word translates but you need something to tell you how to build sentences and get the grammar correct.

Assume you have a spreadsheet fred.xls with a list of names and addresses. Row 1 must be the column names (Salutation, Surname, Address Line 1, Address line 2 etc) and rows 2, 3, 4 ... etc are the names and addresses. I used OOv2.0 both to create the file fred.xls and also to print the labels.

Step 1 - register the spreadsheet with OOv2 as a database

FILE > NEW > DATABASE >CONNECT TO EXISTING > choose SPREADSHEET > NEXT > browse for the XLS File > NEXT > YES - REGISTER > FINISH > save as database. This creates a database fred.odb in OO which is linked to fred.xls. If you add a new name to fred.xls, it will be seen by OO when you create the labels.

Step 2 - create a LABELS document

This requires that you define (or choose) how many labels are on the sheet, and what fields you want on the labels

File > New > Labels - this creates your label document, and opens the LABELS box for you to define which fields go into the label, and how many labels on the sheet

a) which fields do you want in the label?

LABELS tab > DATABASE > choose fred.xls
> TABLE > choose Sheet 1
> DATABASE FIELD > highlight SALUTATION and click left arrow - this puts <....salutation> in the INSCRIPTION field. Repeat for all the fields you want in the label. You can edit the INSCRIPTION (eg by adding spaces or enter for a spare line). You can put two fields on one line.

b) how many labels on the page?

- choose the LABELS tab.
- Set the SHEET button, leave ADDRESS and CONTINUOUS not set
- If you are lucky choose a brand that matches your sheet of labels
- if not define your own by choosing TYPE as User
- choose the FORMAT tab
- define how your labels are set out on the sheet (see HELP button for definitions). Be sure that the totals add up to less than the page size (I use A4 labels and the page is 297mm x 210mm)
- choose the OPTIONS tab
- Set the ENTIRE PAGE button, leave SINGLE LABEL and SYNCHRONISE not set
- be sure that the OPTIONS > PRINTER > SETUP > PROPERTIES are correct, especially PAPER SIZE (A4)
- go back to FORMAT tab and SAVE your page definition for future use

Click NEW DOCUMENT and this creates your labels page(s) - at present it has no names in it - just field names

Step 3 - set up the page size, font and import the names and print

- choose the font and font size you want
- FORMAT > PAGE - note it is set to USER. Choose A4
- FILE > PRINT > The message box says "do you want to print a form letter?" choose YES. This opens the mail merge box
- select the names for the sheet. Left click on the empty grey box to the left of the first name you want, scroll down, hold SHIFT and left click the last name. The names and addresses are highlighted
- select OK and the labels get printed.

Step 4 - cleaning up

If you have experimented and you want to delete the connections to the spreadsheet, then TOOLS > OPTIONS > DATABASES > highlight and choose DELETE. This deletes the OO link to fred.xls - it does not delete the file itself.


1 There are no doubt other / better / faster methods but this worked for me
2 If you click the SYNCHRONISE box, then when you get to Step3 it fills all 24 labels with the same data from Row 2 of the spreadsheet.
3 Messing with the page size caused OO to think it was prining the labels on an A6 page and most of the A4 sheet was blank...
4 I have posted this as a separate post because I thought people would probably search on LABEL and SPREADSHEET

Happy Christmas to all label-ers!
LO, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Posts: 7269
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Producing Address labels

Postby ruthlessrider » Wed Jun 29, 2016 3:42 pm

Thanks, I'll try it.
OpenOffice 4.1.2
Posts: 4
Joined: Tue Jun 28, 2016 11:52 pm

Return to Advanced Uses

Who is online

Users browsing this forum: No registered users and 2 guests