Excel To Word Macro

March 19, 2002 - by Bill Jelen

Thanks to Jake who provided this week's Excel question:

How can I write a macro which will take Excel spreadsheet data and create a Word file for each row of data?

Jake - what a great idea! I often have sales results for the entire company and it would be great to be able to send each rep just his or her information in Word. You could do this from Word using Mail Merge, but controlling Word from Excel is an interesting prospect. The example below is fairly straightforward, but one would be able to build upon this concept to do fairly advanced applications.

First, let's take a look at a sample data set. I created a workbook with two sheets - one called Data and one called Template. The Data worksheet has many rows of data. The Template worksheet has the structure of the Word Document that I want to create. In this case, I will want to copy the name from column A of the database to cell C4 on the template. Columns B:E of the database will go in cells C10:C13.

First, let's take a look at a sample data set. I created a workbook with two sheets - one called Data and one called Template. The Data worksheet has many rows of data. The Template worksheet has the structure of the Word Document that I want to create. In this case, I will want to copy the name from column A of the database to cell C4 on the template. Columns B:E of the database will go in cells C10:C13.

Start the VB Editor with alt-F11. Since we want to issue Word commands here, Go to Tools > References. Scroll down to find "Microsoft Word 8" and check the box next to select this item.

To control Word from Excel, you need to define a variable to represent the Word application. In the example below, I used appWD. For any commands in the Excel Macro which you want to apply to the Word application, you simply prefix the command with appWD. In fact, since I have never written a line of Word Macro code before, I went to Word, recorded the actions, then copied that code into Excel, adding the prefix before each line.

Sub ControlWord()
' You must pick Microsoft Word 8.0 from Tools>References 
' in the VB editor to execute Word commands.
' See VB Help topic "Controlling One Microsoft Office Application from Another"
' for more information.
' Originally published by www.MrExcel.com 2/28/1999
    Dim appWD As Word.Application
    ' Create a new instance of Word & make it visible
    Set appWD = CreateObject("Word.Application.8")
    appWD.Visible = True

    Sheets("Data").Select
    'Find the last row with data in the database
    FinalRow = Range("A9999").End(xlUp).Row
    For i = 2 To FinalRow
        Sheets("Data").Select
        ' Copy the name to cell C4
        Range("A" & i).Copy Destination:=Sheets("Template").Range("C4") 
        ' Copy data columns, transpose and paste in C10:C13
        Range("B" & i & ":E" & i).Copy
        Sheets("Template").Select
        Range("C10").PasteSpecial Transpose:=True
        ' Copy the data for the new document to the clipboard
        Range("A1:F15").Copy
        ' Tell Word to create a new document
        appWD.Documents.Add
        ' Tell Word to paste the contents of the clipboard into the new document
        appWD.Selection.Paste
        ' Save the new document with a sequential file name
        appWD.ActiveDocument.SaveAs FileName:="File" & i
        ' Close this new word document
        appWD.ActiveDocument.Close
    Next i
    ' Close the Word application
    appWD.Quit
End Sub

After running this macro, you will have one new Word file for each row of data on your data sheet.

Thanks again to Jake for this great question. There are many applications where controlling Word from inside of Excel could provide a powerful solution.