AUTOMATE WORD FILES FROM EXCEL CONTENT

Recently I completed a project where I had to create hundreds of Microsoft Word documents based off information in an Excel table. This task is easily automated using an Excel macro.

The idea is you create a template in MS Word with some specific text you want to replace . You tell Excel to open MS Word and replace the Template with your Excel data and save the file under a new name. Excel then loops through your data and creates as many new documents as you need. You can also use a simple command to produce PDFs of the new files.

In our example we will edit the below MS Word document and replace the placeholders ‘recipientName’ and ‘productName’ with the values we have stored in an Excel file. In our fictitious example we are in the business of music CDs.

Let’ s walk through this example together. You can download the MS Word and Excel files we will use from the below link.

1. Get excel Ready

Open the Excel file ‘WordAutomation.xlsm’. If you are asked to enable macros click yes. This Excel file uses VBA macros to automate your MS Word file creation. Next, press Alt-F11 to open the script editor .

The first thing we need to do is to add the reference for Excel to manipulate MS Word files. We will need to enable a MS Word library. Click on Tools->References.

Scroll through the list and look for ‘Microsoft Word 16.0 Object Library’. If you have an older version of Microsoft Office it may be a different version number e.g. ‘Microsoft Word 12.0 Object Library’ which will still be fine. Go and select the check box and click OK. You may need to save the document and then reopen the file for the change to take effect.

Note, If you cannot see any code, make sure you select ‘Module1’ on the left hand side of the screen.

2. Excel review

Before we review the code, let’s start with what we have in the Excel sheet. Go back to the tab ‘WordAutomation’. Firstly, note that the red text is the data we modify to change the variables of the macro.

Under section ‘1. Automation Variables’, we define a start and end row to run. This enables us to select which rows of data we want to use to create new Word files. For example if start_row is 1 and end_row is 2, we will create files for customers 1 and 2 only.

 

Next you will see the directory of our template and the name of our Word file. In your case you will replace the directory name of where you placed your file. Remember to complete the directory location with a ‘\’.

 

Under section ‘2. Data’, you will see the data we will use in each Word file. The first column is a customer number, which is used for the file name e.g. 1.docx will be the file for the first customer. Then we have the recipient name we will replace and the product that the customer bought.

 

Lastly, the green button ‘Run Automation’ on the top of the screen is a shortcut to run the Macro.

3. What's the Code Doing?

Let’s understand what this code is doing. The first part of our script (parts 1 and 2 set up some variables we will need. We then define a new Word object to edit via ‘Set objWord = CeateObject (“Word.Application”) ‘ . Next we define the variables to edit, which are the strings ‘recipientName ‘ and ‘productName ‘. After this we make the Word application visible so we can see what we are doing through ‘objWord.Visible = True’.

Now we are ready to assign our variables to values in the Excel sheet. In part 4 of the script, we add 19 to the start and end customer numbers to get the row reference for the data. For example customer 1 is on row 20. After this we assign the template name and the file location.

Sub wordAutomation()
    
    '1. Global Variables
    Dim ws As Worksheet
    Dim objWord As Object
    Dim i As Integer
    Dim strValue As String
    Dim filename As String
    Dim start_num As Integer
    Dim end_num As Integer
    
    Set ws_word = ThisWorkbook.Sheets("WordAutomation")
    Set objWord = CreateObject("Word.Application")
    
    '2. Replacement Data Variables
    Dim recipientName As String
    Dim productName As String
    
    '3. Make Word Visible
    objWord.Visible = True
    
    '4. Set Global Variables
    start_num = ws_word.Cells(9, "B").Value + 19
    end_num = ws_word.Cells(10, "B").Value + 19
        
    Template = ws_word.Cells(13, "B").Value
    SaveLocation = ws_word.Cells(14, "B").Value

 

Section 5 of the code loops through our Excel data. Part 5.1 opens the Template document and defines the values for recipientName and productName. In part 5.2 and 5.3 we search for any references in the Template Word document for ‘{recipientName}’ , ‘{productName}’ and replace these with the variables assigned in the Excel table. In part 5.4 we save the updated changes to the customer number e.g. 1.docx.

In part 5.5 we export the file to a PDF as well using the ‘objWord.ActiveDocument.SaveAs’ function. This is handy if you want to send PDFs rather than Word documents. Lastly, in part 6 we close MS Word.

'5. Loop Through the Customer Data
    For i = start_num To end_num
    
        '5.1 Open Template File
        objWord.Documents.Open Template
    
        objWord.Activate
        recipientName = ws_word.Range("B" & i).Value
        productName = ws_word.Range("C" & i).Value     
                
        '5.2 Replace Recipient Name
        With objWord.Selection.Find
            .Text = "{recipientName}"
            .Replacement.Text = recipientName
            .Execute Replace:=wdReplaceAll
        End With
        
        '5.3 Replace Product Name
        With objWord.Selection.Find
            .Text = "{productName}"
            .Replacement.Text = productName
            .Execute Replace:=wdReplaceAll
        End With
              

        '5.4 Save Document as New File Name
        Save = SaveLocation & i - 19
        objWord.ActiveDocument.SaveAs Save


        '5.5 Save to PDF
        objWord.ActiveDocument.SaveAs SaveLocation & i - 19 & ".pdf", 17

        '5.6 Close Active Word Document 
        objWord.ActiveDocument.Close

        Next i

'6. Close Word
objWord.Quit
Set objWord = Nothing
   
End Sub

Well done! You now have a powerful way to automate the creation of MS Word documents using a data set within Excel. With a few adjustments, you can easily apply this process to your own automation requirements.

 

Leave a Comment