OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Create address labels from a spreadsheet of addresses- OO2.0
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Writer
View previous topic :: View next topic  
Author Message
John_Ha
General User
General User


Joined: 05 Dec 2005
Posts: 34

PostPosted: Mon Dec 05, 2005 12:53 pm    Post subject: Create address labels from a spreadsheet of addresses- OO2.0 Reply with quote

After many happy hours Smile of experimentation and searching the FAQ (especially HELP ME WITH LABELS PLEASE Dec 05 2005), I have finally managed to get my Christmas card labels printed off by the following method. The help is indeed very 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.

Miscellaneous
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 priningt 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!
Back to top
View user's profile Send private message
paustian
Newbie
Newbie


Joined: 14 Apr 2005
Posts: 3
Location: University of Wisconsin-Madison

PostPosted: Tue Dec 06, 2005 2:09 pm    Post subject: Thanks Reply with quote

Thank you for posting this. I have been trying to do this in Neooffice an have run into a problem where it refeses to update a spreadsheet. I think I am going to try it in OpenOffice 2.0 tonight.
Back to top
View user's profile Send private message Visit poster's website
godboutm
General User
General User


Joined: 08 Dec 2005
Posts: 9
Location: New Hampshire

PostPosted: Thu Dec 08, 2005 5:33 am    Post subject: Reply with quote

I have a form letter I often use. I saw the "want to print a form letter" dialog once when printing a letter, and it worked fine then, but never since that time. Now when I try to print, it only prints the template with the field names, not the data that's supposed to be in the fields. The dialog box doesn't show up.

I can still print the letter correctly using the Wizard, but that's kind of a pain in the butt.

So how do I get print to recognize that it's a form letter again?

Thanks.

--Marc
Back to top
View user's profile Send private message Visit poster's website
howard
OOo Advocate
OOo Advocate


Joined: 28 Feb 2004
Posts: 333
Location: Newfoundland

PostPosted: Thu Dec 08, 2005 7:30 am    Post subject: Reply with quote

John,

Why not print your addresses directly onto the envelopes as I do. That way I get the sender's address and a blue airmail sticker (if needed) and the addressee all printed at the same time, without the cost of labels.

godboutm,

I think you must have clicked on the "never ask me this again" button - a very unwise thing until you are really familiar with the program! Sorry I don't know how to reverse it.
_________________
Howard
3.41 (Vanilla) on various versions of PCLinuxOS and 4.01 on W8.1 (hardly ever used!)
Back to top
View user's profile Send private message
godboutm
General User
General User


Joined: 08 Dec 2005
Posts: 9
Location: New Hampshire

PostPosted: Thu Dec 08, 2005 7:43 am    Post subject: Reply with quote

Thanks, John, but I don't think I did. I almost never do that on purpose, for that reason. But even if I did, I know I selected the merge option. Usually if you say not to ask again, it picks the last thing you selected. In this case, it's deciding not to merge.

--Marc
Back to top
View user's profile Send private message Visit poster's website
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Thu Dec 08, 2005 9:07 am    Post subject: Reply with quote

Marc,

Unfortunately I know of no simple way to get the "form letter" query back but here's what I have previously suggested which involes forcing OOo to rebuild you "user" directory. The user was succesful.
>>>
Well I think I have a solution for you but it is not he best in the world. Follow the procedure I suggest here:
http://www.oooforum.org/forum/viewtopic.phtml?t=27098

As you will see when you view the link you are going to lose all personal setting which will include your macros, if any. You can save macros, but not dialogs, to text files prior to doing this by going into the Basic IDE. Then load your macro and use the save button over at the right to save the file.
>>>
This is a fairly drastic solution and if you want to experiment you could try just forcing a rebuild on one of the subdirectories within the user folder. There is a "config" subdirectory which contains some other possible subdirectories.

If you try this please report back with your results.
Back to top
View user's profile Send private message
godboutm
General User
General User


Joined: 08 Dec 2005
Posts: 9
Location: New Hampshire

PostPosted: Thu Dec 08, 2005 5:02 pm    Post subject: Reply with quote

Thanks, John. That appears to have worked. It wasn't as drastic for me, though. First of all, I don't have any macros. And secondly, I still had the old OOo1.x user files, so it just imported my old settings. It lost all the knowledge of the new database, but that was easily fixed.

Thanks again.

--Marc
Back to top
View user's profile Send private message Visit poster's website
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Fri Dec 09, 2005 7:46 am    Post subject: Reply with quote

Marc,

Quote:
It lost all the knowledge of the new database, but that was easily fixed.
I'm curious as to exactly what this means and I'm sure not going to go through the procedure to find out Wink

It seems that a Base file either simply links to an existing source such as a spreadsheet or contains an actual database creating from with the Base application. In either case the .odb file is store in a directory outside the "user" one.

I assume you didn't lose any of these files but mearly had to do someting like Tools > Options > Base > Databases > New to re-register the existing files as Data Sources. Is this correct?
Back to top
View user's profile Send private message
godboutm
General User
General User


Joined: 08 Dec 2005
Posts: 9
Location: New Hampshire

PostPosted: Fri Dec 09, 2005 8:16 am    Post subject: Reply with quote

Quote:

Marc,

Quote:
It lost all the knowledge of the new database, but that was easily fixed.

I'm curious as to exactly what this means and I'm sure not going to go through the procedure to find out Wink


John, I'm certainly no expert on OOo, and much less one of databases, but from going through the database process, here's what I think happens.

I had a MySQL database before upgrading to OOo2.0. OOo2 handles databases differently. First, it can access MySQL as a database type, through ODBC or JDBC. With OOo2 I had to "create" a new database. In reality, it connected to my MySQL database and created a .odb file in a directory of my choice. I don't know what's in the the .odb file, but I assume it's the connection information to my existing database.

In addition to creating this new file, I had to register the "new" database with OOo. This seems to allow the database to show up when you press F4. I think this registration is what was lost when I wiped out my user directory. So I had to "create" the database all over again. I'm guessing I could have gone into Tools-Options-Base-<other stuff> to re-register the database, but I only discovered that after I created the new database. Too bad, though, because I did have some queries and those were lost in the rebuild. No biggy, as they were simple queries.

I moved my user directory, instead of deleting it, and tried copying back the user/database directory after rebuilding user in the hopes that it would have restored my old connections. But that didn't do a darned thing.

The only other thing of interest here is that the import of my old 1.x information contained my original database information. it still shows up when I hit F4, but I can't connect to it. I'm tempted to delete that reference, but am afraid it might decide to connect and delete the database itself.

I hope this makes sense. It does to me Smile
Back to top
View user's profile Send private message Visit poster's website
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Fri Dec 09, 2005 8:56 am    Post subject: Reply with quote

Marc,

Makes sense to me also.

I'm thinking that if you did not overwrite your original .odb file (I probably would have) when you reestablished your connection to MySql that you could register it again and retrieve your queries. This is in theory, of course, because I can't try it and don't want to be able to! I may be wrong however because I believe that in 1.1.x your querys were store somewhere in the user directory. Don't know if they have been moved to the .odb file in v2.
Back to top
View user's profile Send private message
godboutm
General User
General User


Joined: 08 Dec 2005
Posts: 9
Location: New Hampshire

PostPosted: Fri Dec 09, 2005 9:38 am    Post subject: Reply with quote

Quote:

I'm thinking that if you did not overwrite your original .odb file (I probably would have) when you reestablished your connection to MySql that you could register it again and retrieve your queries.


I think so, too. And I'm not too inclined to experiment further either. Wink I seem to have things working right now and am a happy camper once again.

Thanks for the help.
Back to top
View user's profile Send private message Visit poster's website
bobmcg99
Newbie
Newbie


Joined: 10 Dec 2005
Posts: 1

PostPosted: Sat Dec 10, 2005 10:17 am    Post subject: Thanks for label info Reply with quote

Just wanted to thank you for the spot-on help in creating labels from a spreadsheet. I did my share of hair pulling trying to figure it out, but you saved me from total baldness. Well written and very useful. Thanks.

bobm..............
Back to top
View user's profile Send private message
sparky
General User
General User


Joined: 06 Nov 2005
Posts: 9

PostPosted: Sat Dec 17, 2005 8:30 pm    Post subject: How to Undo "never ask me again" Reply with quote

This method may not work for everything, but it's not as drastic as losing all your macros. I should mention that I'm using OOo 2.0 for Windows, so the Linux version may not work exactly the same.

Anyway, after regretting checking one of those "don't ask me again" boxes, I had Windows identify every file that had changed that day. This led me to a bunch of .XCU files in C:\Documents and Settings\Tom\Application Data\OpenOffice.org2\user\registry\data\org\openoffice\Office. In the Writer.xcu file, I found an entry called something like "Ask for Merge" which was set to "false". I made a copy of the original Writer.xcu file as a backup, renamed it to WriterOLD.xcu, then changed "false" to "true" and saved the edited version as Writer.xcu. Now I have my polite, helpful message box back like before. Apparently, those .XCU files hold all the detailed configuration changes that don't show up in the "Options" menu.

This method might be a little dangerous, since I don't know what side effects may result, but it seems to have worked! And I've got my backup file if need be.
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Sun Dec 18, 2005 11:06 am    Post subject: Reply with quote

sparky,

This is very good information to have but when I (OOo2.0.0) look in Writer.xcu I can find anything close to "Ask for Merge". Can you be a little more specific or did I just miss it?
Back to top
View user's profile Send private message
howard
OOo Advocate
OOo Advocate


Joined: 28 Feb 2004
Posts: 333
Location: Newfoundland

PostPosted: Mon Dec 19, 2005 8:03 am    Post subject: Reply with quote

I can't find "Ask for Merge" in mine either - maybe it's because I have never clicked on it.

Incidentally, yesterday I deleted the whole OOo2.0 folder from my "application data" folder and started again (by clicking on OOo writer in the programs list in explorer) because of the disappearing font problem I was having. Apart from having to reset all my personal settings and preferences there seems to be no problem (so far!) with such a drastic step, and it fixed my problem, so it was something in there that had got screwed up.

I have also discovered that when I selected an odc spreadsheet as a database for my Christmas envelopes a copy is created as an odb (in "My Documents" - there could be a better place!) file and it works from that. You have to use the "exchange databases" feature in "Edit" to do this.
_________________
Howard
3.41 (Vanilla) on various versions of PCLinuxOS and 4.01 on W8.1 (hardly ever used!)
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Writer All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group