Excel Macro to Create Label Template in Word

Posted on 2009-12-18
Last Modified: 2012-06-21

I have an Excel document which lists a load of names and addresses. I would like to create a script that would create a new Word Document, insert a standard label template (e.g. for Avery Labels L7163) insert the correct number of pages required (e.g. there are 14 labels per page for this label so if there are 42 addresses it would create three pages of labels) and then inserts the addresses from Excel into the Word document template.

This would be used in Office 2003 using ideally VBA.

Any starter on this would be greatly appreciated !..


Question by:gisvpn
    LVL 10

    Expert Comment

    Why use VBA - Word and Excel have "Mail merge" which is a built in way of doing exactly as you describe.

    Author Comment

    Ideally it needs to be as simple as possible for the end user (i.e. click a button), the VBA script will also have some validation in to ensure all the information is present and correct before sending it over to Word ;)
    LVL 76

    Accepted Solution

    Sorry I couldn't contribute any earlier. My central heating boiler packed up. It must have been the snow. Thus I was otherwise occupied and also it was too cold to type.

    You can still use mailmerge. Create a labels type of merge main document in the Word User interface. Show the Mail merge toolbar. There is a Mail Merge wizard (Tools/Letters and Mailings) to take you through the steps. When it is working satisfactorily, you can save it and use it again later.

    This snippet uses early binding so you need to set a reference to the Microsoft Word Object library.

    It opens Word and the main document, and runs the merge using the same datasource.
    Sub RunMerge()
        Dim wdApp As Word.Application
        Dim wdMainDoc As Word.Document
        Dim wdResultDoc As Word.Document
        Set wdApp = CreateObject("Word.Application")
        wdapp.visible = true
        Set wdMainDoc = wdApp.Documents.Open("C:\MyFolder\MyMainDoc.doc")
        With wdMainDoc.MailMerge
        End With
        Set wdResultDoc = wdApp.ActiveDocument
        wdMainDoc.Close wdDoNotSaveChanges
    End Sub

    Open in new window


    Author Comment


    Thanks for the post, I will try this. Im not sure yet on what is above is doing but I may have some questions ;)

    Are you based in the UK?


    LVL 76

    Expert Comment

    OK, good lcck.

    Yes I am in UK (Surrey)

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now