Mail merge from spreadsheet to labels template

[cthetutor]

How can I mail merge using a spreadsheet to oo label template? My only choices are letter or e-mail.

[Villeroy]

File>New>Labels

[cthetutor]

Did that. how do I MERGE my spreadsheet with my label?
The options do not allow me to choose ANYTHING from the database, Table or Database field

[Villeroy]

You have a registered database and a template?
Registered database: https://forum.openoffice.org/en/forum/v ... 75#p397375

Manual creation without wizard:
Insert>Fields>Database ... and insert the required placeholder fields into your first label.
Append a final [Next Record] field.
If your template shows a [Synchronize] toolbar, hit the button to copy the label acros the sheet. Otherwise copy and paste manually.

[TFWieb]

I'm having a similar problem. After loading the fields into the template, the instructions for Mail Merge say: "Click New Document. You now have a new single page document containing a series of frames, one for each label ...." I'm using OO 4.1.3 on a PC/Windows 10 and my New Document displays only a single frame rather than a series across and down the page (see attached screen shot). Is this a problem with Windows 10? I did the same operation on an older computer 6 months ago and it worked seemlessly. Would like to know if I'm doing something wrong or, if not, if there is a work-around. Thanks for your help! - Tom

[Villeroy]

Really? You make a screen shot of your Writer document, wrap the picture into another Writer document and attach that to your posting?

[John_Ha]

Search the Writer forum with label or merge - you will find lots of posts with advice. Remember you need three things:

1 A spreadsheet with the data - typically a .ods file

2 A registered database file - .odb file. It "provides the interface between Writer and the spreadsheet" and mail merge / labels will not work without it.

3 A Writer label document.

Full instructions are given in Chapter 11 - Using Mail Merge in the OOo v3.3 Writer Manual.

If this solves the problem, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[TFWieb]

To Villeroy - Not sure I understand your question. The Writer document generated from the template (my screen shot) is a multi-page document with one label on each page. What I want, of course, is a page with multiple labels.

To. John - I believe I have all three of those elements. In fact, I've been following the directions from Chapter 11 of Using Mail Merge. Have gone back through it multiple times and not been able to find where I'm doing anything wrong.

[John_Ha]

Try Re: Can't fill address labels from spreadsheet found by searching the forum with mail merge.

If this solves the problem, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[John_Ha]

Is it a Java problem?

If you use the Wizard, you must have Java 32-bit installed for AOO even if you already have Java 64-bit installed. See Mail Merge

[Villeroy]

No Java problem. Your screen shot indicates that you have a registered database "TFW_Lichens" linked to some spreadsheet with a sheet named "Sheet1" having a list with column headers "Species", "Author" etc.
Just print that.

Edit: Just print that text document with the mail merge fields.

Answer the question if you want to print a serial letter with "yes" and then see what happens. You can print to a file (Writer or PDF). If that fails, tell us what happens instead.

Hit F4 for the data source window.
There you see your "TFW_Lichens". Right-click>Edit Database... if not, tell us about it.
At the bottom of the database window you see which spreadsheet that database is connected to.
The "Tables" section shows the spreadsheet tables, e.g. "Sheet1". If not, tell us about it.

[TFWieb]

Yes, my TFW_Lichens database is linked to a spreadsheet named Lichen_Collections_Spreadsheet.ods. Not sure why I would want to print that, but when I do, it does not prompt me as to whether or not I want to print a serial letter. Printing to a file shows the full spreadsheet split over a number of pages since it is wider than a standard page.

Data source window - Right click>Edit Database shows the same spreadsheet at the bottom, ie. Lichen_Collections_Spreadsheet.ods
The Tables section lists Sheet1. When I click on it, the spreadsheet fills in the panel to the right. This is where I've been with past efforts. When I select six lines for printing, it generates a six page document with one label on the top left of each page, one for each of the records selected.

Checking on JRE - Tools>Options>Java says JRE is installed already, but does not list it. A search for it gives me a path. If I try to Add it, when I navigate to this location, it tells me there is no JRE in the folder. By the way, it is in a downloaded app as follows: downloads>Fiji-win64>FijiApp>Java>win64>jdk1.8.0_66>jre

Sorry for the delay between replys. I'm doing a lot of outside work and just come in occasionally to check on this.

[Villeroy]

Sorry, I mean: print the text document that is represented in your screenshot. Print it to another file (saves ink and paper) and see.

[Villeroy]

The attached document is made from scratch without any wizard and with no Java technology involved.
The data source is table "biblio" of the "Bibliography" data source that is shipped with your office suite, so you can generate merged output on your machine (unless you removed the Bibliography database).

The layout is a downloaded template from http://www.worldlabel.com/Pages/template_1.htm These downloadable templates consist of text tables with the right spacing for the tiled labels on the paper sheets. You should turn on View>Non-Printing Characters (Ctrl+F10) in order to get the paragraphs, lines and table cells. I added red table borders which should be removed before printing. In this template each row of cells consists of 5 cells. #2 and #4 are just space holders.

I simply edited one of the table cells, added some database fields from the "biblio" table in "Bibliography" plus a last field [Next]. If you can't see the [Next] field, hit Ctrl+F9 (menu:View>Field Names). The [Next] field tells the program to fetch the next record. Without the [Next] field you would get one page per record, each page with identical label content. This would be OK with business cards but here I added the [Next] field to my master cell.

Finally I copied the content of the edited master cell into all the other table cells. Creating this file did not take more than 5 minutes.

Edit: Removed further customisation tips

[John_Ha]

To. John - I believe I have all three of those elements. In fact, I've been following the directions from Chapter 11 of Using Mail Merge. Have gone back through it multiple times and not been able to find where I'm doing anything wrong.

Did you register the data source as described on page 302 of Chapter 11?

[TFWieb]

Yes, the data source is registered. I've been referring to a PDF of Chapter 11 which uses it's own page numbers. I've followed the directions that begin on page 5 of the chapter.

One thing that I feel is not explained well in the chapter comes in the section which describes how to remove blank lines. At the end, it cautions: "The last paragraph of the label address block ends with a special field, Next record:Database.Table (Next record:Points.Sheet1 in our example), and the Hidden paragraph field MUST be inserted before this field." It's not clear to me what this should look like. I inserted "Next record" from the Database tab found when you go to Insert>Fields>Other, then I used the left arrow to move back in front of this field and inserted a hidden paragraph with the following conditions: ![Next record:TFW_Lichens.Sheet1], then clicked of the Synchronize button. Other frames are still not added to the page, but when I print from this document, it gives me all the labels I've selected, but alas, each on a separate page as before. I kept thinking the issue related to the Next Record field, but since the multipage document that is generated does show the same data on each page, this doesn't seem to be the case.

[TFWieb]

Villeroy - I'm probably not savy enough to follow your suggested options, but I have gone back to something you suggested earlier, which is to copy and paste the single frame that I do have. This seems to be working, but I'm having some trouble getting as many labels on a page. Can I adjust the frame that I have, or do I need to go back through the process and create a new frame with user-defined parameters? My instructions don't cover that, but I'm feeling tike I might muddle through it with a little trial and error. The original issue is still a puzzle, but this may be a suitable work-around.

[John_Ha]

What I would do in your situation is to do some separate tests.

Create a simple spreadsheet and register it. Choose a standard label sheet from the templates. Create the labels. Does it work OK?

If it does work OK, what are you doing differently with your proper file(s)?

[Villeroy]

At least you could test the labels I have prepared for you, so see how it is intended to work.

Open the file.
Hit Ctrl+F9 repeatedly in order to see the <placehoder names> and what they refer to.
Print.
Answer "Yes" when prompted if you want to print a form letter. "No" would print the label sheet as shown on screen.
Choose "All Records" and "Output = File" instead of "Printer", specify a file name, confirm the dialog and wait some seconds.
Open the specified file. It contains the result of the mail merge.

The first mail merge fields refer to "Bibliography.biblio.Identifier"
"Bibliography" is the name of some database. You as a Writer user don't have to bother about the location nor type of that database.
"biblio" is the name of a table within that database.
"Identifier" is the name of a column within that table.

Hit F4 (menu:View>Data Sources) in order to open the data source window. It looks like this:

On the left there is a tree view with registered names of databases, tables within these databases and queries within these databases.
Can you see your own data source and tables in the data source window?

On the right side you see table data after you clicked one of the tables.
Can you see your data by clicking on a table name?

If you see your list data in the data source window:
1) Get the right label template from http://www.worldlabel.com/Pages/template_1.htm
2) Fill one of the table cells with mail merge fields, line breaks and with one [Next] field (menu:Insert>Fields>Other...Database).
3) Copy the table cell content into the other cells.

Edit: If you don't see your list data in the data source window:

menu:File>New>Database...
[X] Connect to existing database
Type: Spreadsheet
Specify the spreadsheet file
[X] Register the database
Save, close and forget the database but do not delete it.
Nothing has been copied, converted nor exported by any means. The data are still in the spreadsheet.
After editing the spreadsheet, you have to restart the whole office suite before the changes take place in the labels.

Edit: There are better sources than spreadsheets.

Notice that you can edit the "Bibliography" data source directly in the data source window and any changes take place immediately. The "Bibliography" is connected to a dBase directory. Create a dedicated database directory and save you spreadsheet as dBase file (*.dbf) in that directory. Only the active sheet of a spreadsheet document is saved as one dBase table. Then connect a new database document to the dBase directory or change your existing database document so it is linked to the dBase directory instead of the spreadsheet (menu:Edit>Database>Connection... type:dBase)

[TFWieb]

OK, I do see my table and data. Have downloaded a template and will give this a try. Will report back later.

[TFWieb]

That worked great! The exercise helped me to understand much better how all this works. So, thanks for that.

Since my spreadsheet is pretty simple and is also a registered database, this operation fulfilled your suggested test also, John. As far as I can tell, I did the same thing as before, so I still have no idea why this wasn't working for me from within OpenOffice. In any case, I'm considering the issue solved. Thanks very much!

[John_Ha]

Since my spreadsheet is pretty simple and is also a registered database ...

You need three files for labels:

1 A spreadsheet .ods file with the data
2 A database .odb file which has nothing in it but which is registered and can be considered to be the "interface between the document and the spreadsheet"
3 A document .odt file for the labels.

If you do not have the .odb file in Step 2, or have not registered it, it will not work.

I wrote this aide-memoire as a post on another forum many, many years ago. It was based on OOo v2, used a .xls file, and it isn't fully correct ... but it serves me well each Christmas.