« The Immense Potential of the IF Function in OpenOffice Calc | Main | A Ray of Hope Regarding My OpenOffice 2 Book »

July 13, 2006


ok, I now get how to do labels. And I think I understand the link to the database. But, what I don't get is the how to sync the data, which is on a spreadsheet, into the database. I can initially create that link. But let's say, I need to modify the spreadsheet, keeping all of the fields. Just the data that I'm merging has changed. How do I tell the data source to sync up to the latest version of the spreadsheet. It seems that it should be automatic, given the connection, but I don't see it updating.



I created a database (from File>New...) from a spread sheet I have already filled with info.

Like you say, I didn't think it was updating when I made changes. But when I closed the database, and then re-opened it, and double clicked on one of the tables (e.g. "Sheet1"), the changes had gone through.

If this isn't working for you, try right clicking on a table in your database (e.g. "Sheet1") and choose Database > Connection Type. Then 'Spreadsheet' from the drop down menu. Then select your spread sheet, and click 'Test Connection'.

Great start - it all works as advertised. Only one little problem here: It seems impossible to get Open Office to populate more than one page (sheet of labels) at a time. For instance, if you are printing 300 addresses on labels that come 6 per page, it seems necessary to re-populate the ONE page 50 times by hand to do it.

I must be missing something. My co-worker in the cubible next door, who originally found the problem, must be missing the same thing. Any clues? (Open Office 2.1 on WinXP.)

Been struggling with the "only one sheet" problem my self. THe trick is in this bit:

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

I don't think it will play if you just hit the "print it now" button...

One problem:
It's not vertically centered. How? How to delete the second row too? It's useless!

And I think it will be much better if we can see the record value than seeing

As if we click the "Data to Fields" button from Data Source View. It's disabled if I dont do the 15th step. Is it bug or something else?

I'm using OOo v 2.0 for Windows.

Another question: how to show the "Mail Merge" window? Mine isn't looking like that, there is no "Record" and "Output" dialog.

Thx in advance.

I figured this part out already, BUT
A) How do you get it to do other than default fonts without having to twiddle every label? and
B) How do you get it to put the Post Office bar code on the label. If you're bulk mailing, this is importanr!

This step in the process does not occur for me:

>>Choose File > Print.

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

Not that it does not work, I never get the message. I choose "print" from the file menu. The "print" dialog box appears, I click print, and it prints (and not the information I desire either).

I am running OO 2.0, have even reinstalled it, and nothing. I have spent hours upon hours trying to get it to print 700+ labels from a DB. I cannot find anything related to a "form letter" when dealing with labels in Writer.

My DB is fine, it is connected to the open document in Writer and all the other steps go smoothly other than:

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

Which as said before, does not happen.

Any help would be greatly appreciated, as I am going crazy trying to get this to work, and have no other program to print labels :(

Hi Adam,

See this blog entry.

I couldn't find the file but it seems to work for everyone else.


Thanks SOOOO much Solveig! You saved me :) That blog entry covered my problem. The config for the message pop-up was set to "false". Changed it to "true", saved the file and was in business. Thanks again!!

Glad it worked! That's one of the settings that I think should be under Tools > Options, at least, or off of the Print Options button, not buried deep in the bowels of the XML.


All my address dbs are as tables in Word (having installed Open Office only recently when I upgraded to Vista). Is there a way I can get a link to them to produce labels -or do I have the chore of transferring all the information to Open Office? CsZ4u2

Hi Arthur.

Open the .doc files in Writer. Copy the table contents, create a new spreadsheet, and paste. Then create a database based on that new spreadsheet, and you can connect to that database to create labels.



Solveig - many thanks. I created the spreadsheet datablase with ease, but trying to register it produces the message that the file does not exist. What am I doing wrong?

Hi Arthur,

Just be sure the spreadsheet is the right format (spreadsheet not word), that the data is headings in the first row and data in the 2nd and subsequent rows, and be sure the file is closed.

By trying to register, you mean saving in the final window of the File > New > Database wizard?


Solveig - The spreadsheet database is filed in OpenOffice.org Calc and formatted the way you describe. I used 'HELP Registering and Deleting a Database' to register the file so that I could access its contents for producing labels (but I can't).

Another conundrum: If I enter an Avery Label type (8160) should it automatically format?

Thanks again

Solveigh, thanks for your helpful page!

Now I've got another problem: I want to print labels using 436 records, so I check all records in the dialog box, but OO decides to leave out records (always the same) and I end up with only 156 labels (13 sheets with 12 labels each). I also tried 1 to 436 records in the dialog box - the result is the same.

Any idea? Writing all the adress labels for the shipping of the christmas parcels wouldn't be so funny *sheepish smile*

Found the solution: the field "next record" was inserted more than once!

HOW many times have I done that? Yep, that's a stickler. I have modified my approach so that I make the font for that tiny tiny gray box nice and big, so it's easier to see doubles.

Glad you found the solution!


Solveig - any suggestions for Arthur (yet again!) message of November 14. Like Suse, I do not relish the prospect of writing out 100s of Christmas address labels.

Hi Arthur,

I'm not sure what you mean by automatically format, I'm afraid.

I think there must be something wrong with the spreadsheet. Copy and paste the content into a news spreadsheet and save it in .ods, then create a new database, be sure to specify that it's of type spreadsheet, and leave the checkbox in the last window, saying you want to register the database, marked. That should work.


S - I appreciate - and admire - your patience! I have again done as you said, but in 'Labels' I still cannot get Sheet 1 to show in the Table window, nor the address fields in Database. In 'Database' after creating the neww database where do I specify it is a spreadhseet, as I cannot see such an option in the the Database Wizard?


Done all that, labels printing OK from databse file.
Is there a quick and easy way to get it not to print empty fields?

Done all that, labels printing OK from databse file.
Is there a quick and easy way to get it not to print empty fields?

Hi Ian,




This doesn't work. I make labels, it sees the fields, I print to a file, and it prints 28 copies of the same label. Also, I can't just select a bunch of labels to format at once. If I actually send the labels to the printer, it does run them through a file, one sheet at a time, so I only have the last one. At that time, the label is read-only, so I can't change it. At this point, I would gladly pay 50$ for a copy of Microsoft word, if such a thing would run un linux. Creating labels there is easy.

I've spent hours today trying to format the address labels for avery 5160! It doesn't seem to matter what I put in it won't print correctly. Also it is not showing all of my tables and it won't delete the one I was using! Aghhhh!

Hi and thank you so much for your site, I learned a lot. I would like to see how to prevent only one address from printing per the page (over and over). thanks in advance.


I have tried to print label but without success. Finally I got it right this time.

I have tried and tried to get the label info to come up... did all the things suggested and still get "First, Last, Address, City, St, Zip" showing on the sheet for printing


I appreciate your posting so much helpful information. I just wish it worked. Sounds so good, however, I've notice that a good number of other people are not able to get Open Office to work either. I have struggled of days trying to simply print mailing labels from, spreadsheet, database, text document, and more. Does not seem possible!
Looks like I will need to splurge this weekend on Microsoft Office 2007 in order
to print some labels. I would have liked to support the idea of a free and open program, but once again, this seems more like a wish than a reality. Simply can't afford the time to work with programs that don't work.

See also this video.


this is a great instruction well done, helped me a lot as a newcomer on OOo label-printing! Many thanks for your help.

I can't get the data to show when I go to print it doesn't ask me if to print a form letter please respond

Thank you for this - a great help!

Thank you for this - a great help!

Thanks for this handy how-to. I spent quite a while looking around for one this succinct.

I have followed the directions, but I get one solid sheet of one address, not a sheet of all records. All records are selected, but entire sheet is Jeff Jones, help?

Been trying to 2 years to merge labels now!
Never works!
The closest I have gotten is to get to where it shows the description of the fields as in the window above and where you indicate that is how it should be. NOT SHOWING THE ACTUAL ADDRESSES! Then I follow through with the rest of the steps and print and only get a print out of the fields without the data.

Have wasted hundreds of dollars worth of ink and paper and worked with an instructor at a local community college where they use the Open Office. He has agreed that they too will quit using it if this doesn't work.

Any money saved over the cost of Microsoft is lost in wasted time and supplies. Since most people are familiar with Microsoft, this is an unnecessary wast of time that can be use for course work.

I have tried to work with this program and the tech support and it seems to be fruitless. I will show the correspondence and information to people to let them know of the frustration working with this program before the decide to purchase their computers. This way they will be able to get the package with Microsoft Office included and save hours of headaches and disappointments.

I have worked on printing labels for days and can't get it to work. Every thing goes well until I hit print. Then I get the form letter question and answer yes. It tell me the data source can't be found. I try to select the right source but it never works. I have reinstalled twice still the same. The data source it refers to have been deleted long ago.

Any one else? Any solution.

Thanks Solveig, it works perfectly!

I'm using OpenOffice.org 3.0 on Vector Linux 5.9 with Avery A4 J8160 mailing labels, 21 per sheet.

Just to be sure, I first printed a test page (on scrap paper) and that was good but the formatting was not as I wanted it. To correct the formatting I went back to Step 1 and created a new "Label" file and used the "Format" tab at step 9. I then saved the format for next time.

By the third sheet of scrap paper, everything was as I wanted it and the actual label printing went without a hitch.

When I printed, I only printed one page at a time by selecting "From" (under "Records") "1 to 21" and "22 to 42" etc, and manually fed the Avery sheets in. I felt uneasy with selecting "All" records.

I made the spreadsheet containing the data, with the handy DataForm extension. It makes things so easy. Like I have done with this tutorial, I followed your instructions step-by-step (from the first link at the top of the page "instructions for spreadsheets). A couple of times I tried to second-guess your instructions but you were always right.

My advice to those who are having problems: make sure you are following the instructions exactly.

Hi Bruce,

I'm glad it worked! The tweaking can be a bit of a pain and you're right to be cautious with the printing at first.

I'll check out the Data Form extension.


Hi Solveig -

Just wanted to thank you for this post. I am a long-time MS user who recently switched to OO and started to get frustrated trying to do a mail-merge since the interface was a bit different. Googled it, found your site, and now I've got my labels and can move on to more important stuff :)

All the best,


Hi Adam,

Glad it worked! It's not obvious, but I find it quite reliable.


Very Nice,Thanks for sharing


I done the the above about 10 times now, and I get 30 pages of labels, instead of 30 different contacts labels on one page. What am I missing?


Thanks you thank you thank you... It is midnight and I dont know how many more hours I would have wasted had it not been for this tutorial.

OMG!!!! Thank-you Solveig!! You are a genius...I have been working on this for HOURS. The sychronize button was the trick! This stuff is so complicated, and I surely thank-you. I have my labels and now I am ready to go on to my next project. Thank-you, thank-you...Freda

Thanks for your tutorial. In Open Office 3.1 everything works but I cannot change the fonts or print size!Can you help?

I'm sorry we didn't read your blog earlier. I wish we read this page four hours ago. Thank you, thank you, thank you!

The comments to this entry are closed.

GetOpenOffice Consulting

Get Book Resources

Search This Blog