Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Canton, Ohio, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge text into Avery labels

    I work in a law firm and this is a rush job - I have approximately 800 addresses set up in address blocks on a Word 2002 document with one blank line between the addresses. Some addresses are three, four, or even five lines. I need to send out large envelopes which require labels, and I would like to use Avery 5162 size labels. I cannot figure out how to set up a merge like that. I know how to merge tables, but this text block is blowing my mind. Can anyone help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge text into Avery labels

    What is the relation to Woody's Office Power Pack (WOPR)?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge text into Avery labels

    Since you mention it is a rush job, I'll assume it is a Word question (this thread may be moved to the Word forum.) Here is a very rough macro. Make sure that there is a blank line after the last address and that the address document is the active document when you run it. There may be an empty row at the end of the labels when the macro has run. You can delete it manually.

    Sub ProcessLabels()
    Dim docSrc As Document
    Dim docTrg As Document
    Dim n As Integer
    Dim f As Boolean

    On Error GoTo ErrHandler

    Application.ScreenUpdating = False
    Set docSrc = ActiveDocument
    Set docTrg = Application.MailingLabel.CreateNewDocument(Name:=" 5162", Address:="", _
    AutoText:="", LaserTray:=wdPrinterManualFeed, ExtractAddress:=False, _
    PrintEPostageLabel:=False, Vertical:=False)
    docSrc.Activate
    Selection.HomeKey Unit:=wdStory
    n = 1
    Do
    Do
    Selection.MoveDown Unit:=wdLine, Extend:=wdExtend
    Loop Until Asc(ActiveDocument.Characters(Selection.End - 1)) = 13
    Selection.MoveEnd Unit:=wdCharacter, Count:=-2
    Selection.Copy
    docTrg.Activate
    Selection.Paste
    Selection.MoveRight Unit:=wdCell, Count:=n + 1
    n = (n + 1) Mod 2
    docSrc.Activate
    Selection.MoveRight Unit:=wdCharacter, Count:=3
    Loop Until f Or (ActiveDocument.Bookmarks("Sel").Start = ActiveDocument.Bookmarks("EndOfDoc").Start)
    docTrg.Activate
    ActiveDocument.Paragraphs.LeftIndent = 7
    ActiveDocument.Paragraphs.RightIndent = 7
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    f = True
    Resume Next
    End Sub

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Canton, Ohio, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge text into Avery labels

    Thank you, Hans! I found out today that we do not need to do this merge in the near future. Therefore, I have some time to try out your macro. I really appreciate your help. I will let you know how I am making out. It's good to know there is someone out there to get you out of a pinch (and so quickly too). Thanks again. Johanna

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge text into Avery labels

    An alternative that doesn't require macros is to simply turn your current format into a tab separated file <img src=/S/grin.gif border=0 alt=grin width=15 height=15> (and then a table if you wish)

    Use the Edit > Replace box

    Step 1 - search for the blank line identified by two paragraph marks (these are represented by ^p^p in the find box) and replace with something unique e.g. XYZZY. The resulting file will look a bit weird but that gets fixed at Step 3

    Step 2 - turn the remaining paragraph marks into tabs (^p replaced by ^t).

    Step 3 - turn the XYZZY back into paragraph marks - (XYZZY replaced by ^p)

    Step 4 - check that first and last records are OK and change them by hand if not

    Step 5 - use this file directly for your merge or if you prefer change it to a table via table> convert> text to Table

    Variations on this trick can be used for a vast number of reformatting problems. HTH

    Note - sometimes those two paragraph marks also have single spaces between them - a Step zero looking for "^p " and turning it to "^p" repeated until there are no more to replace sorts this out.

    Technote: The ^ character is just {Shift/6} and the p and t characters must be a lowercase p and lowercase t.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge text into Avery labels

    Hi Andrew,

    Your technique is elegant and it can indeed be used in many reformatting problems. Two additions:

    1. Instead of entering "^p " in Find What (and "^p" in Replace With) and repeatedly click Replace All until nothing is found, enter "^p^w" in Find What. Only one Replace All will suffice. The code ^w stands for "white space" (any combination of spaces, non-breaking spaces, and tabs)

    2. Depending on the way the file was created, Johanna may have to insert a step 2.5: turn manual line breaks ^l (lower case L) into tabs ^t.

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Canton, Ohio, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge text into Avery labels

    Hi Andrew. Your replace strategy worked fine. I set up the label merge and each label had the NextRecord code in it, except the first one. What I don't know how to do is put the first label merge code in. Has it something to do with the field or record delimiter? Whatever merge code I put in, it turns into a blank document after the merge. What am I doing wrong?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge text into Avery labels

    Use step 4 of the Mail Merge task pane or the Insert Merge Fields button on the Mail Merge toolbar to populate the first label. Then click "Propagate Labels" in the task pane or on the toolbar to copy the mail merge fields to the other labels.

  9. #9
    Lounger
    Join Date
    Jan 2001
    Location
    Canton, Ohio, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge text into Avery labels

    Hans. This is the greates macro ever! I finally had a chance to try out your macro, and it's marvelous! If I get a raise for that (which I doubt), I'll share it with you! I know all the girls in the office will be using it frequently.

    Thank you soo much - you are the greatest!

    Johanna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •