Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Label Order (VBA Word 2003)

    Hi,

    Ive a vba macro that mail merges data into labels and orders them alphabetically according to address line two and works nicely. However I now need to order by the next line down (address line Three) which starts with a text code i.e. qwe, space then a mixed text/numeric code, i.e. asd.12345, so the whole line looks like...... 'qwe asd.12345'

    Ive adjusted the macro syntax accordingly from address 2 to address 3, but it doesnt seem to want to order according to the first three text letters (qwe)?

    Any ideas ?

    Cheers.
    Lee <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: Label Order (VBA Word 2003)

    I'm afraid it's impossible to tell without seeing the relevant files.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Label Order (VBA Word 2003)

    Hi Hans,

    Heres a copy of the source file, which Ive had to put as a text file to attach to this post, and Ive included the script in case you need it.

    Sub Merge()
    'AOne 28171 alternative label
    Dim docMerge As Document
    With Application.MailingLabel
    .DefaultPrintBarCode = False
    Set docMerge = .CreateNewDocument( _
    Name:="Good to Go", _
    Address:="", _
    AutoText:="ToolsCreateLabels3", _
    LaserTray:=wdPrinterTractorFeed, _
    ExtractAddress:=False, _
    PrintEPostageLabel:=False, _
    Vertical:=False)
    End With
    With docMerge.MailMerge
    .MainDocumentType = wdMailingLabels
    .OpenDataSource _
    Name:="C:solLabels Filelabels.csv", ConfirmConversions:=False, ReadOnly:= _
    False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:= _
    "", Revert:=False, Format:=wdOpenFormatAuto, Connection:="", SQLStatement _
    :="SELECT * FROM 'labels.csv' ORDER BY CITY", SQLStatement1:="", SubType:=wdMergeSubTypeOther
    ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
    wdFieldAddressBlock, Text:= _
    "f ""<<_TITLE0_ >><<_FIRST0_>><< _LAST0_>><< _SUFFIX0_>>" & Chr(13) & "<<_COMPANY_," & Chr(13) & ">><<_STREET1_" & Chr(13) & ">><<_STREET2_" & Chr(13) & ">><<_CITY_" & Chr(13) & ">><<_STATE_" & Chr(13) & ">><<_POSTAL_>><<" & Chr(13) & "_COUNTRY_>>"" l 2057 c 2 e ""U"
    WordBasic.MailMergePropagateLabel
    docMerge.Activate
    ' With ActiveWindow.Selection ## in case and extra row is needed
    ' .InsertRowsBelow 1
    ' End With
    Selection.WholeStory
    Selection.Font.Size = 18.5
    Selection.Font.Bold = wdToggle
    Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
    ActiveDocument.Tables(1).Range.Cells.VerticalAlign ment = wdCellAlignVerticalCenter

    WordBasic.MailMergePropagateLabel
    .Destination = wdSendToNewDocument
    With ActiveDocument.Tables(1).Rows
    .HeightRule = wdRowHeightExactly
    .Height = CentimetersToPoints(3.75)
    End With
    .Execute
    '.Destination = wdSendToPrinter
    '.Execute
    End With
    'MsgBox "Ensure you delete the labels csv doc"
    'ActiveWindow.Application.Quit SaveChanges:=False


    Cheers.
    Lee

  4. #4
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Label Order (VBA Word 2003)

    Hans,

    I forgot to say that its ordered by City not address3 as originally stated.

    Cheers.
    Lee

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

    Re: Label Order (VBA Word 2003)

    Apparently, Word is trying to be intelligent - since the "cities" end in a numeric code, it sorts on those numbers instead of a straightforward alphanumeric sort.

    Any chance you can split the "city" into separate fields in the data source ?

  6. #6
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Label Order (VBA Word 2003)

    Do you mean put into a separate column? And have the alphanumeric in a separate column?

    Maybe put the first three text letters in a column headed 'City' and the mixed alpha numeric in a column headed as 'Postcode' and in the label have them both on the same line (Is that possible?), i.e. 'City Postcode'......... but sorted by 'City'?

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

    Re: Label Order (VBA Word 2003)

    Yes, if possible put the first three characters in a separate field City, the rest in Postcode, and sort on City. You'd have to move the bits in the f switch for the ADDESSBLOCK field around, or use separate MERGEFIELD fields.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Label Order (VBA Word 2003)

    Excellent.

    Now works. I fiddled a little with the relevant script line which Ive posted below in case it helps anyone else. This puts the 'City' and 'Postcode',(Postal), on the same line with a space in between despite it being in separate columns in the data source, and finally sorts that whole line into alpha order by 'City'.

    "f ""<<_TITLE0_ >><<_FIRST0_>><< _LAST0_>><< _SUFFIX0_>>" & Chr(13) & "<<_COMPANY_," & Chr(13) & ">><<_STREET1_" & Chr(13) & ">><<_STREET2_" & Chr(13) & ">><<_CITY_ >><<_POSTAL_>><<" & Chr(13) & "_COUNTRY_>>"" l 2057 c 2 e ""U"


    Many Thanks for your help Hans.

    Best Regards.

    Lee <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Label Order (VBA Word 2003)

    You can even use <<_CITY__POSTAL_>> if you wish.

  10. #10
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Label Order (VBA Word 2003)

    Ok thanks for that Hans.

    Incidentally, is it possible to change the font size on one of the lines, being 'Last', is it something like.....'<<_LAST_" & Font(20) & " >>' ?

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

    Re: Label Order (VBA Word 2003)

    I don't think that's possible. You'd have to insert separate MAILMERGE fields instead of a single ADDRESSBLOCK field, and format those fields.

  12. #12
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Label Order (VBA Word 2003)

    Ok.

    Thanks for all your help anyway.

    Best Regards.
    Lee

Posting Permissions

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