Excel Macro to Create Label Template in Word


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 !..


Who is Participating?
GrahamSkanConnect With a Mentor RetiredCommented:
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

Why use VBA - Word and Excel have "Mail merge" which is a built in way of doing exactly as you describe.
gisvpnAuthor Commented:
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 ;)
gisvpnAuthor Commented:

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?


OK, good lcck.

Yes I am in UK (Surrey)
All Courses

From novice to tech pro — start learning today.