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.

  1. Use FoxPro, import the Excel data, use automation with Word to create the labels
  2. 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.
  3. Write an Excel Macro to scan through the data and automate Word to create the labels.
  4. The code could be written in Foxpro, Word, or Excel.
  5. 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

      oExcel=CREATEOBJECT("excel.application")

      cFile="d:\calvinh\ba\Christmas Local Addresses Dec 2004.xls"

      oExcel.Workbooks.Open(cFile)

      oWord=CREATEOBJECT("word.application")

    With oWord as WORD.Application

        .Visible = true

        .Documents.Add

        .MailingLabel.DefaultPrintBarCode = False

        oLbls=.MailingLabel.CreateNewDocument("8160","", ;

            "ToolsCreateLabels1",.f., wdPrinterManualFeed)

* ExtractAddress:=False, PrintEPostageLabel:=False, Vertical:=False)

            .Documents(2).Close(false)

    ENDWITH

      nRow=2 && first row has no data: just column headings on some spreadsheets

      WITH oExcel

            nCol=1

            DO WHILE .t.

                  oRange=.Range("a"+TRANSFORM(nrow))

                  IF ""=oRange.Text

                        EXIT

                  ENDIF

                  cName=.Cells(nrow,1).Text

                  cAddress=.Cells(nrow,2).Text

                  cCSZ=.Cells(nrow,3).Text

                  WITH oWord.Selection as WORD.Selection

                        .TypeText(cName)

                        .TypeParagraph

                        .TypeText(cAddress)

                        .TypeParagraph

                        .TypeText(cCSZ)

                        .MoveRight(wdCell)

                        nCol=nCol+1

                        IF nCol=4

                              nCol=1

                        ELSE

                              .MoveRight(wdCell)

                        ENDIF

                  ENDWITH

                  nRow=nRow+1

            ENDDO

      ENDWITH

It was fairly straightforward to translate this to VB for an Excel macro which could be used.

Open the spreadsheet in Execl

 

Choose Tools->Macro->Macro

 

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.

 

 

 

Sub Labels()

    Dim oWord As Word.Application

   

    Set oWord = CreateObject("word.application")

    With oWord

        .Visible = True

        .Documents.Add

        .MailingLabel.DefaultPrintBarCode = False

        Set oLbls = .MailingLabel.CreateNewDocument("8160", "", _

            "ToolsCreateLabels1", False, wdPrinterManualFeed)

        .Documents(2).Close (False)

    End With

‘ 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

            Exit Do

        End If

        cName = Cells(nRow, 1).Text

        cAddress = Cells(nRow, 2).Text

        cCSZ = Cells(nRow, 3).Text

        If "" <> cAddress And "" <> cCSZ Then

   

            With oWord.Selection

                .TypeText (cName)

                .TypeParagraph

                .TypeText (cAddress)

                .TypeParagraph

                .TypeText (cCSZ)

                nCol = nCol + 1

                .MoveRight (wdCell)

                If nCol = 4 Then

                    nCol = 1

                Else

                    .MoveRight (wdCell)

                End If

            End With

        End If

 

        nRow = nRow + 1

    Loop

 

End Sub

 

 

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.

45517