Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Burpengary, Queensland, Australia
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Label Structure (97 & 2000)

    Hi everyone.

    I have posted this on the VBA forum too, so hopefully one (or many) of the gurus can help!!

    I am endeavouring to create labels using an outside database source and an .rtf file.This is sending me <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    I have two choices with the rtf file (as I see it <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15> ):-

    1. create a single label and programmatically delete the empty labels on the sheet after processing;
    2. create the .rtf file as a table and programmatically merge this file with a label file;
    3. get one of the gurus to explain to me how else I can do it <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    Unfortunately, I cannot see what is behind the labels so therefore can't program for the deletion of same.

    If anyone could help me, that would be great.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Burpengary, Queensland, Australia
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Structure (97 & 2000)

    Hi again! I figured it out! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    The steps were as follows:-

    * I adjusted the .rtf so that the result was 1 set of data per page.
    * I then programatically deleted the extra page break
    * converted my document into a table
    * added a header row
    * renamed my document as a predefined data source
    * opened a predefined label file
    * merged the result

    and voila - perfect labels every time.

    If anyone would like to see the code - please reply and I will post it!

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Structure (97 & 2000)

    Hi Trish:
    I'm always interested in seeing a macro--even if I don't understand it. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. Please post it.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Burpengary, Queensland, Australia
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Structure (97 & 2000)

    OK - here goes:-

    ' Macro recorded 12/08/02 by Trish Holland
    '
    Dim iCounter As Integer
    iCounter = 0

    ' This part finds the code for the iCounter to make the correct number of rows for the table
    Selection.HomeKey
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
    .Text = "~!m!~"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    While Selection.Find.Execute = True
    iCounter = iCounter + 1
    ' This deletes the selected text "~!m!~"
    Selection.TypeBackspace
    Wend

    ' This searches for the codes in the rtf to signal a paragraph marker and hard page break and deletes them
    Selection.HomeKey
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
    .Text = "^p^m"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    ' This selects the whole document and turns it into a table
    Selection.WholeStory
    Selection.ConvertToTable Separator:=wdSeparateByTabs, NumColumns:=4, _
    NumRows:=iCounter, Format:=wdTableFormatNone, ApplyBorders:=True, ApplyShading _
    :=True, ApplyFont:=True, ApplyColor:=True, ApplyHeadingRows:=True, _
    ApplyLastRow:=False, ApplyFirstColumn:=True, ApplyLastColumn:=False, _
    AutoFit:=False
    Selection.HomeKey Unit:=wdStory
    ' This inserts a new row at the beginning and places the merge fields in the first row
    Selection.InsertRows 1
    Selection.TypeText Text:="Name"
    Selection.MoveRight Unit:=wdCharacter, Count:=1
    Selection.TypeText Text:="Address 1"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="Address 2"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText Text:="Suburb"
    ' This saves the document so that it can be merged properly
    ActiveDocument.SaveAs "wp32opdocsworkinvitelabels.doc"
    ActiveDocument.Close

    ' This opens the precreated label file
    Documents.Open FileName:="wp32opdocsworkinvitelabelsform.doc", ConfirmConversions:=False _
    , ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto
    ' This performs the mail merge
    ActiveDocument.MailMerge.OpenDataSource Name:= _
    "wp32opdocsworkinvitelabels.doc", ConfirmConversions:=False, _
    ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:="", SQLStatement:="", SQLStatement1:=""
    With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .MailAsAttachment = False
    .MailAddressFieldName = ""
    .MailSubject = ""
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=True
    End With

    ' This changes to the precreated labels form and closes without saving
    Windows("Invitelabelsform.doc").Activate
    ActiveDocument.Close (False)

    End Sub

    I'm sure there is probably a cleaner way - but - you work with what you've got.

Posting Permissions

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