Sending Christmas cards: Creating mailng labels automatically
A family member sent out an emergency email plea for help:
I bought labels: Avery 8160, which have very clear instructions about how to enter names and addresses manually, but nothing about how to use data from an Excel file to do so.
Avery.com tells me what fancy things to buy. Excel Help only explains about its own internal addresses. Word allows me to outline and print labels, but help only explains how to import items from Excel into a text.
Can someone please tell me which buttons push?
I replied that if he sent me the spreadsheets, I could find a solution for him. After all, I’m the family member who actually *works* for the company that makes these great products Excel and Word!
I started dreading that the data was not well formatted for labels. For example, is all the data in a single Excel cell ? Is it all in separate cells? Is there a separate first name, last name, title, city, zip column? How about spouse names (Mr. and Mrs. John Doe)? Are there columns for email address? How much work would have to be done to clean up the data so it has a consistent format?
As it turns out, the data was fairly well formatted for labels. Column 1 was exactly formatted as “Mr. and Mrs. John Doe”, columns 2 & 3 were exactly formatted for labels as “123 Anywhere St”, “City, State Zip” so there wasn’t much cleanup to do.
There were 3 spreadsheets: Local to Hawaii (where I used to live and most of my family still does), Overseas (which to Hawaiian people means non-US), and Mainland US.
After a little thought, I came up with a few possible solutions.
- Use FoxPro, import the Excel data, use automation with Word to create the labels
- Import the data to Outlook (using Foxpro or Excel) and then write an Outlook macro: this meant he’d have to stop storing data in Excel and start using Outlook.
- Write an Excel Macro to scan through the data and automate Word to create the labels.
- The code could be written in Foxpro, Word, or Excel.
- I could create the label document somehow and email it back to him, but that meant he depended upon me every time he needed a new set of labels.
Solution 3 seemed the simplest, but I chose to prototype the automation in FoxPro because I can manipulate live objects (like an Excel range or a Word Selection object) from the command window and the intellisense would be very helpful. Also, Foxpro doesn’t require pre-registering COM objects, like VB, Word or Excel to get Intellisense. Intellisense requires an accurate typelibrary. However, the typelibrary could specify that a method returns a generic object (could be a cell or a range or a value perhaps), and then no intellisense will show for that. In VFP, the intellisense will work because it’s manipulating a live object, and when its instantiated, the type information can be obtained dynamically. Also, the powerful VFP debugger allows you to single step the code, examining the values and even executing further commands.
I used the VFP Object Browser to show the Word TypeLibrary to get some of the constants, like wdPrinterManualFeed an wdCell
The 8160 label sheet has 3 labels across, so the loop has to do a single MoveRight after the 3rd column.
This is the FoxPro prototype code:
#define TRUE .t.
#define FALSE .f.
#DEFINE wdPrinterManualFeed 4
#define wdCell 12
PUBLIC oWord as word.application
PUBLIC oLbls as WORD.Document
PUBLIC oExcel as excel.application
cFile="d:\calvinh\ba\Christmas Local Addresses Dec 2004.xls"
With oWord as WORD.Application
.Visible = true
.MailingLabel.DefaultPrintBarCode = False
* ExtractAddress:=False, PrintEPostageLabel:=False, Vertical:=False)
nRow=2 && first row has no data: just column headings on some spreadsheets
DO WHILE .t.
WITH oWord.Selection as WORD.Selection
It was fairly straightforward to translate this to VB for an Excel macro which could be used.
Open the spreadsheet in Execl
Give the macro a name, like “labels”, then click “Create”. The Visual Basic Editor comes up
Choose Tools->References then put a checkmark on Microsoft Word 11.0 Object Library, then OK (yours might have an older version number than 11.0)
Make the code look like below by pasting it in. Then hit the F5 button.
It automates Word to create a mailing label document, scans through the spreadsheet and fills in the labels.
Dim oWord As Word.Application
Set oWord = CreateObject("word.application")
.Visible = True
.MailingLabel.DefaultPrintBarCode = False
Set oLbls = .MailingLabel.CreateNewDocument("8160", "", _
"ToolsCreateLabels1", False, wdPrinterManualFeed)
‘ nRow = 2 ' first row has no data: just column headings on some spreadsheets
nCol = 1
Do While True
If "" = Range("a" & nRow) Then ' blank cell in first column ends loop
cName = Cells(nRow, 1).Text
cAddress = Cells(nRow, 2).Text
cCSZ = Cells(nRow, 3).Text
If "" <> cAddress And "" <> cCSZ Then
nCol = nCol + 1
If nCol = 4 Then
nCol = 1
nRow = nRow + 1
On the phone, he had an older version of Word and Excel, so the “MailingLabel.CreateNewDocument” line didn’t compile correctly. I didn’t have the older version of Word handy, so I asked him to start Word, record a Macro, choose Tools->Letters and Mailings->Envelopes and Labels, specify Avery 8160, then New Document. (That’s how I figured out many of the lines of the code above: recording a macro.) We inspected the Word macro, copied/pasted it into the Excel macro, and he was very happy to see the Word label document being filled automatically with the Excel address data.