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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.