DISQUS

Eric Peter · 1 year ago

I have a list of 800+ names and addresses in the spreadsheet format [ A1-Name, B1- address 1, C1- address 2 ] and I want to change it to [ A1- Name, A2- address 1, A3- Address 2 ] etc and put them into labels like the ones you used up there... is there anyway or a program I can use to achieve this without having to copy and paste every single label?
I appreciate your help...
Eric,

Rob hayden · 1 year ago

Yes, I need to do the same thing. It's easy in Word, you just use the mailmerge function which lets you pull from numerous data sources into a label template. Perhaps Writer needs an extension.

Your problem with the spreadsheet itself needs a different solution though, I knocked up an example macro that should reformat the content (don't run this without saving a copy!) the way you need it, AS LONG AS every name and address really is 3 lines long. It assumes the data is in column A of sheet1.

sub RealignAddresses
rem ----------------------------------------------------------------------
rem define variables

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object

Doc = thisComponent
Sheet = Doc.Sheets(0)

dim namerow as integer
dim address1 as integer
dim address2 as integer
dim oldvalue as string
destinationrow=0
for f=0 to 800 step 3

namerow=f
address1=f+1
address2=f+2

rem--------name-----------------
SourceCell = Sheet.getCellByPosition(0, namerow)
oldvalue = SourceCell.String

DestinationCell = Sheet.getCellByPosition(0,destinationrow)
DestinationCell.String = oldvalue

rem--------address1-----------------
SourceCell = Sheet.getCellByPosition(0,address1)
oldvalue = SourceCell.String

DestinationCell = Sheet.getCellByPosition(1,destinationrow)
DestinationCell.String = oldvalue

rem--------address2-----------------
SourceCell = Sheet.getCellByPosition(0,address2)
oldvalue = SourceCell.String

DestinationCell = Sheet.getCellByPosition(2, destinationrow)
DestinationCell.String = oldvalue

destinationrow = destinationrow+1
next f

end sub

kalll · 10 months ago

On a first look it doesn't seems that difficult although I admit I never made labels before, I usually go with avery labels but I would like to try making few, I need some new models. Thanks for the little tutorial!

CEM n ATL · 9 months ago

I'm attempting to print several address labels using Writer where the address information is contained in a spreadsheet. I have successfully printed labels before but this is how it worked for me:
In Writer the Avery label default lets say for 5962 defines the page size as "User" measured 8.55" x 10.18". My Canon Imageclass sounds the alarm recognizing this is not going to work when I am printing onto an 8" x 11" page. Logical solution - change to page size to Letter - did it - results - entire column (2 of 2) drops off the page leaving only 1 of the 2 columns to print.

WorldLabel.com is a great resource place to learn the dimensions of your particular label. I really hope OpenOffice.org realizes this is a problem and issues a fix right away, this manual manipulation to get the label size just right is a big pain in the you know what.

I am finding that once I close and open the same document later (lets say the next day or later that same day) to print the merged data, it has made all 14 labels of the first page the same thing where as before label 1 - 14 contained exclusive merged data (the goal). I am very careful not to click "Yes" when asked upon opening the document to "Update All Links" as I understand this will cause the same merged data to print on an entire page of labels.

Any fixes for this coming Open office?

Swing Trading · 1 month ago

Insightful read. I have just bookmarked this at stumbleupon. Hope others find it as interesting as I did.