Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Mailing Labels (Word 2007)

    I'm having a bit of trouble creating mailing labels with VBA in Word 2007. In 2003 and previous, I've used the following:

    Application.MailingLabel.CreateNewDocument Name:="5161"

    Generally, the user types 5161 or selects it from a list if a frequently used label format is required. 5161 is a label with 2 columns and 10 rows.

    In Word 2007, the above code gives me a table with 4 columns and 8 rows. If I record a macro to create this label type, the recorded code is:

    Application.MailingLabel.CreateNewDocumentByID LabelID:="1359804672"

    Word 2007 Help has the following example: Application.MailingLabel.CreaNewDocument Name:="2160 mini", but in practice, that raises an error.

    Is it no longer possible to create a label sheet based on a standard label number? Is there a table for "translating" label names (i.e. the Avery number) to the new LabelID numbers?

    Thanks,
    Richard

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

    Re: Mailing Labels (Word 2007)

    Apparently, there is more than one label type with number 5161, so which one you get is unpredictable. Word 2007 Mailmerge with some label types doesn't give expected output explains why this happens in Word 2007. Unfortunately, I find the suggestion on how to calculate the label ID rather cryptic.

    Cindy Meister has another suggestion in CreateNewDocument creates labels with wrong page size and margins, but that's a rather ugly workaround... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: Mailing Labels (Word 2007)

    Thanks. I got the same label format (4 col x 8 rows) for quite a few label "names" (i.e. numbers) that I tested. It seems less like duplication of numbers and more like a bug. The 4 x 8 label sheet looks square, but if I check paper size, it's letter. Strange. Just as strange is the fact that the sample in the Help File (which appends "mini" to the label number) raises an error.

    My VBA application in question originally had 3 options: select label from a list, enter the label number, or use the active label document. I removed the last option, but I think it's time to re-introduce it. If all else fails, the user can create a label sheet using Word, and proceed from there.

    Avery is such a standard, it's hard to imagine that MS decided not to at least make it the default. At one time the Avery Label Wizard was the most popular download on the MS and Avery websites. Non-Avery brand labels generally reference the Avery product that they mimic.

    I suppose this is progress. :-)

    Regards,
    Richard

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: Mailing Labels (Word 2007)

    The code in the KB article uses label type 5160 as an example and results in a label with 4 columns and 12 rows. Avery Label 5160 should have 3 col and 10 rows. Google results show only Avery selling a label 5160. This looks like a bug to me. I've reviewed the XML files in the PAGESIZE folder. The Avery XML (partner ID 81 for US paper sizes) indicates the correct "num across" and "num down" values. Apparently when Word's label feature reads the XML, the resulting label sheet is correct. When VBA read the XML, the results are not correct. It doesn't seem logical that VBA would read from a different source. Label 5161 has a product ID 5161 in the XML. Label 5160 is not listed in the XML, but 15160 is... with the coorect values. for label type 5160. Maybe this is a work in progress.

    Nothing beat WordPerfect/DOS for labels!

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

    Re: Mailing Labels (Word 2007)

    I agree that it is a bug in the way VBA handles labels. Apparently the transition to XML isn't as smooth as hoped.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Los Angeles, California, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailing Labels (Word 2007)

    "Nothing beat WordPerfect/DOS for labels!"

    Nor footnote handling, nor sorting, nor automatic numbering, mail-merge, etc., etc. Face it, by comparison, Word's an uncivilized beast.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: Mailing Labels (Word 2007)

    I've been somewhat obsessed with the mailing label situation in Word 2007 over the last couple weeks. Two of my most popular VBA solutions depend on user selection of label size.

    I've searched the .xml files in the PAGESIZE folder. The various label "names" (such as 5161, an Avery label name/number) can all be found. None of the 10-digit "LabelID" codes which appear in a recorded macro are to be found in the .xml files. There is a "ProductID" for each label, but it does not resemble the LabelID.

    The .xml file is definitely used as the source of the info that appears on the Mailing Label dialog box. Word apparently composes the LabelID according to a secret formula or uses the same (or similar) internal method as was used in previous versions of Office. I would guess that Word "reads" the label name and then does what it has always done. VBA, on the other hand, wants to be "au courrant" and tries to get info from the .xml that isn't really there. A few label sizes are created as expected. The majority of them result in the same 4 col x 8 row label sheet on 13" w x 12" h paper.

    Parsing the .xml file, as Cindy Meister (I think) suggested does not seem to provide the answer. I've collected the most likely label sizes to be requested, recorded a macro, and noted the resulting LabelID. As a fail-safe option, I allow the user to run the macro based on the label which is open. The user can create a mailing label interactively, and then proceed with the macro. A bit awkward, but it gets the job done until further progress gets in the way. :-)

Posting Permissions

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