[Solved] Can't fill address labels from spreadsheet

[SAEndicott]

I'm having trouble merging a spreadsheet to print address labels. I can't tell if the problem is with Writer or Calc. I'm using version 4.1.3 on Windows 7.

I have a spreadsheet that I use to maintain addresses for postcards we mail every couple of months. This has worked fine for a few years but stopped working last time. When I try to do this, I create a new Label file in Writer. I can select the Database but the Table drop down is empty. I have three sheets in the spreadsheet but none of them show in the Table drop down.

Looking for similar problems in the forum, it looks like I need to re-register my database. I went to Tools->Options->OpenOffice Base->Databases. My spreadsheet file is in the list. I tried creating a new database entry by pointing to the same spreadsheet and giving it a new Registered Name. I got the same results with the new one.

In case the spreadsheet was corrupted, I saved the addresses as a .CSV file, created a new spreadsheet and read in the .CSV file. I left the sheet as the default name (Sheet1). That file gave me the same results as the other one.

Next, I went to View->Exchange Databases. Neither of the files I tried before would show a list of sheets in the spreadsheet so the Define button stayed disabled.

I noticed that (somewhere along the line), a new file appeared in my Documents folder. It has the same name as my file with a "0" appended to the name. I can see the sheets in this file and click Define button. When I do this, nothing appears in the Databases in Use list. When I create a new label file, I can select that file as the Databse and also select the Table/Sheet I want. I then create a new label document. When I try to print that document, it asks me if I want to print a form letter but when I click Yes, I get a Mail Merge dialog with no records.

I saw some mention of File->New->Database but my menu doesn't have that.

I'm out of ideas. Can anyone see what I'm doing wrong or what might have caused it to stop working after years of using it.

Steve

[thomasjk]

Its possible that the AOO user profile is corrupt. Try resetting the user profile https://forum.openoffice.org/en/forum/v ... 74&t=12426. Then recreate your database. You can copy and paste %appdata%\Libreoffice\4 in the address bar in File Explorer to quickly navigate to the user folder.

[John_Ha]

Do a test by starting from scratch with a new spreadsheet and a new database file. Does it work? If so, the registration of your original database has probably failed.

See Chapter 11 - Using Mail Merge in the OOo v3.3 Writer Manual.

This is my aide-memoire for doing labels. It is very old but basically correct.

Printing Xmas labels next year where all the setup has been done before

0 Check names in "Xmas Card List - Spreadsheet.XLS" (ignore database). Sort into order for those needing labels. CLOSE AND REOPEN XLS file - VERY IMPORTANT.

IF YOU MAKE ANY CHANGES after now, be sure to CLOSE AND REOPEN both XLS and odt files to refresh the database. The XLS file must be open.

BE SURE to read Step 5 - it tells you how to count 21 labels for a 7 x 3 sheet

2012: Now that I am using a smaller font and 7 x 3 labels, I have
space for 5 lines so I don't need 2 x fields on one line. Push all fields in the spreadsheet to the left.

0 Open the database file here, then open the spreadsheet ...

1 Use AVERY L7160 7 x 3 labels with 5 lines per address

- Use the 7 x 3 labels Template L7160 (Ryman P21)
- in the spreadsheet arrange all the fields to the left

2 If using 8 x 2 labels
- open "Xmas Card List - document - Avery L7162 8 x 2.odt".
If using 7 x 2 labels
- open "Xmas Card List - document - Avery L7163 7 x 2.odt"
If using 8 x 3 labels
- open "Xmas Card List - document - Viking 8 x 3.odt"

To create a new OO doc File > New > Label > and choose the label pattern

3 Ignore last years names if they appear in the odt file.

4 Check FORMAT > PAGE and make sure it is set to the correct size (A4) and not User

5 FILE > PRINT (Test by printing to PDF or XPS file)

> It might ask you to register the database if this is a new PC - follow the prompts

- The message box says "do you want to print a form letter?" choose YES.
This opens the mail merge box

- select the names in the xls file. Left click on the empty grey box to
the left of the first name you want, scroll down with arrow if slider
doesn't work, hold SHIFT and left click the last name.
I do one page of labels at a time.

Use SHIFT/click and Ctrl click to add / remove names

Counting how many? See Total as 83 x 16 where 83 is number of names in
the database and 16 is the number selected.

The names and addresses selected are highlighted

- select OK and the labels get printed.

6 "Xmas Card List - TEMPLATE - Avery L7162 A4" is a spare document if anything goes wrong

7 To re-use sheets with some used labels. OO prints from L to R, and then from top to bottom. If the first 5 labels are used, the sheet looks like

1 - empty 2 - empty
3 - empty 4 - empty
5 - empty 6 - NAME
7 - NAME 8 - NAME etc

So,

1 add 5 x blank address lines in the XLS file
2 Save and close the XLS file
3 Close the odt file
4 Open the XLS file - this refreshes the database
5 Open the odt file - this picks up the new database. Choose the addresses to print.

=============================================================================

From scratch...

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 (call it 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 NAME and click left arrow - this puts <....name> 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.

NOTE: For my labels, Address 3 (Col D) and Address 4 (Col E) are on the same line, (seperated by spaces which print) with Address 5 (Col F - POSTCODE ) as a seperate line below. Hence have column E empty in spreadsheet, not column F. Put single blank line at top and 8 spaces in front of all lines for Avery A4 L7162, only 3 for Viking 8 x 3 A4.

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
>>> Viking 8 x 3 A4, Avery A4 L7162
- 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 - at present it has no names in it, just field names

- check FORMAT > PAGE and make sure it is set to the correct size (A4)
and not User

Save this document as Xmas Labels document

==============================================================================

Next year start here...

Open Xmas Labels document - it will have last year's names but ignore them - they get replaced

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

(- choose the font and font size you want >>> Times New Roman 12
(- FORMAT > PAGE - note it is set to USER. Choose A4. >>> OK

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

Option - choose enough names to fill the sheet and save it as Xmas labels 1, 2 etc. You can edit the names.

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.

[SAEndicott]

The replies from thomasjk and John_Ha were helpful. I don't know exactly what solved the problem but it's working now so I'm happy with that.

I tried creating a new spreadsheet and that didn't work either. I then tried resetting the user profile. I got different results from each spreadsheet over several tests. None of the tests worked, though (I just got further sometimes than others).

Then, it occurred to me that I'd never installed Base since I don't use it. I went ahead and installed it in case it mattered. I think that made the difference, though. I had some odd results but, eventually, started to work normally. Both the test and original spreadsheet work and I can get a printout from each.

Thanks for the help.
Steve

[John_Ha]

Then, it occurred to me that I'd never installed Base since I don't use it.

Base is critical! In effect, Base provides the interface between the spreadsheet with the names and Writer so if you don't have it installed, Mail Merge will not work.