none
Office 2010: Excel VBA to create Word label template creates wrong template RRS feed

  • Question

  • Hi,

    I have an old excel file that I did some vba/macro coding for under 2003 which is supposed to open a word document, create a specific label template, then copy data from the excel sheet to the word document.  It worked perfectly fine at that time.

    Some time ago I upgraded to 2010, but haven't had the need to run this code until the other day.  Now I find that it is not creating the correct label template page, even if done from a brand new excel sheet with the only code being what's needed to open, create, and show the word document labels.

    Example code:

    Private Sub Workbook_Open()
        Set appWD = CreateObject("Word.Application")
        With appWD
            .Documents.Add
            .Application.MailingLabel.CreateNewDocument ("5163")
            .Documents(2).Close SaveChanges:=False
            .Visible = True
            .Activate
        End With
    End Sub

    It's supposed to create the Avery label template 5163.  I can go into word and manually select the correct template and that shows correctly (portrait page, laid out 2 labels across by 5 down), but the code does not (landscape page, laid out 4 labels across by 8 down).

    Any insight would be appreciated.

    Tuesday, July 22, 2014 1:48 PM

Answers

  • Microsoft has changed the way that labels are classified. You should use instead

    Dim appwd As Object
    Dim oDoc as Object
        Set appwd = CreateObject("Word.Application")
        With appwd
            .Documents.Add
           Set oDoc = .MailingLabel.CreateNewDocumentByID(LabelID:="1359804674")
            .Visible = True
            .Activate
        End With

    and as it is much faster if Word is already open then

    Dim appwd As Object
    Dim oDoc As Object
        On Error Resume Next
        Set appwd = GetObject(, "Word.Application")
        If Err Then
            Set appwd = CreateObject("Word.Application")
        End If
        On Error GoTo 0
        With appwd
            If .Documents.Count = 0 Then
                .Documents.Add
            End If
            Set oDoc = .MailingLabel.CreateNewDocumentByID(LabelID:="1359804674")
            .Visible = True
            .Activate
            'Do stuff with oDoc here
        End With
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com



    • Edited by Graham MayorMVP Tuesday, July 22, 2014 2:54 PM
    • Marked as answer by FotS Tuesday, July 22, 2014 3:04 PM
    Tuesday, July 22, 2014 2:45 PM

All replies

  • Microsoft has changed the way that labels are classified. You should use instead

    Dim appwd As Object
    Dim oDoc as Object
        Set appwd = CreateObject("Word.Application")
        With appwd
            .Documents.Add
           Set oDoc = .MailingLabel.CreateNewDocumentByID(LabelID:="1359804674")
            .Visible = True
            .Activate
        End With

    and as it is much faster if Word is already open then

    Dim appwd As Object
    Dim oDoc As Object
        On Error Resume Next
        Set appwd = GetObject(, "Word.Application")
        If Err Then
            Set appwd = CreateObject("Word.Application")
        End If
        On Error GoTo 0
        With appwd
            If .Documents.Count = 0 Then
                .Documents.Add
            End If
            Set oDoc = .MailingLabel.CreateNewDocumentByID(LabelID:="1359804674")
            .Visible = True
            .Activate
            'Do stuff with oDoc here
        End With
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com



    • Edited by Graham MayorMVP Tuesday, July 22, 2014 2:54 PM
    • Marked as answer by FotS Tuesday, July 22, 2014 3:04 PM
    Tuesday, July 22, 2014 2:45 PM
  • Great, thanks!  That fixed it.

    Does MS have an article up anywhere that matches the labels with their respective IDs?

    Tuesday, July 22, 2014 3:04 PM
  • I can't find such a listing, but you can use the macro recorder in Word to create the label sheet and thus provide the number.

    Graham Mayor - Word MVP
    www.gmayor.com

    Wednesday, July 23, 2014 4:41 AM