exporting excel data to word template (from excel)

I'm on day 3 of trying to get this to work through applying numerous different approaches I've found online, but none have worked for me. I'm trying to update a word template with data from an excel spreadsheet. The end result is almost identical to what a mail merge would do for me, but unfortunately, I cannot use a mail merge for this exercise.

I've included the sample spreadsheet and the sample template, and any assistance would be helpful.


I'm working with this code, but it is only inserting the employee name:

HTML Code: 

I'm open to the idea of forgoing word all together, and using excel for everything. I envision this could be done by creating a template document on a spreadsheet and then looping through each row to save each file as a PDF, but again, my VBA knowledge is not sophisticated enough to accomplish this.

Thanks very, very much.

Ok, let's start at the top.

Dim Amount, PayDate, Description, EEID, TodaysDate, First, Manager, ManagerTitle, EE, FX As Excel.Range

You would think that this defines all of the variables as Ranges, but in fact, only the last one is actually defined as a Range. The others are defined as Variant. In the grand scheme of things, this doesn't amount to much. If you were to write a large macro and memory was at a premium, it might matter. But, nonetheless, it's good programming practice to correctly define your variables.

So each variable has to be followed with it's type.

Dim Amount as Range
Dim PayDate as Range

or

Dim Amount as Range, PayDate as Range,.....

There is also no need to define each Excel range. You can send the value straight to the Bookmark.

And then there's the bookmarks.

When you write to a Word Bookmark, the bookmark is destroyed. If you ran the code again, you would be greeted with an error because the code can't find the bookmark. If you want to preserve bookmarks, you will have to redefine the bookmark to include the inserted text. But since this is a one shot document, and it's unlikely that you would use code the address the bookmark again, it's not useful to redefine it.

The document you uploaded does not contain any bookmarks. Using Word, you'll have to set the bookmarks again, being careful to use the same names listed in the code. Once this is accomplished, SAVE THE FILE AS A TEMPLATE. Save it into the same folder where your worksheet is. (Word will try to save it with the other templates.) Be sure to use the dotx extension so you'll know that this file is the template.

Change the extension in the Documents.Add line to reflect the name change.

Calling a template will preserve the bookmarks for further use since each Documents.New will generate a Document1 file.

1) loop through cells until it finds a blank one, in order to generate multiple reports at once.

I assume you mean from row 2 to the bottom of the data. This is the way the code below is written.

2) save the file, with file name based on a field, into a folder automatically.

You don't specify WHICH field, so I took a stab at the EmpId and Date from Col P.

This code closes each file after processing. This will speed things up a bit. It you wish for all files to stay open, just comment out the close line.

'Not tested.

Sub SendtoWord()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document

Dim WS As Worksheet
Dim LastRow As Long
Dim A As Long

Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

'Define worksheet
Set WS = Sheets("Spot Bonus Nomination")

'Define last row with data.
With WS
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
'_______________INSERTION CODE______________________________________

'Loop from Row 2 to last row.
For A = 2 To LastRow
     'Create new file based on template.
     Set myDoc = wdApp.Documents.Add(Template:="C:\Users\jhampton\Desktop\Spot Bonus Template.dotx")

    With myDoc.Bookmarks
        'Assign named bookmark with worksheet range.
        .Item("Amount").Range.Text = WS.Range("G" & A)
        .Item("PayDate").Range.Text = WS.Range("H" & A)
        .Item("Description").Range.Text = WS.Range("I" & A)
        .Item("EEID").Range.Text = WS.Range("K" & A)
        .Item("TodaysDate").Range.Text = WS.Range("T" & A)
        .Item("First").Range.Text = WS.Range("L" & A)
        .Item("Manager").Range.Text = WS.Range("Q" & A)
        .Item("ManagerTitle").Range.Text = WS.Range("U" & A)
        .Item("EE").Range.Text = WS.Range("B" & A)
        .Item("FX").Range.Text = WS.Range("F" & A)
    End With
    
    'Save document using EmpID and date from Col P.
    myDoc.SaveAs2 WS.Range("H" & A) & " " & WS.Range("P" & A) & "docx", 51
    'Close file.
    myDoc.Close False
    Set myDoc = Nothing
Next
'___________________________________________________________________

errorHandler:
Set wdApp = Nothing

End Sub