In the previous articles in the "Automating Document Filling" series, I talked about how to create an application's user interface, organize input validation, and get a number in words without using VBA code. In this final article, we will talk about magic - transferring all the necessary values from an Excel workbook to a Word document. Let me show you the end result:
Description of the mechanism
To begin with, I will describe in general terms exactly how data will be transferred to a Word document. First of all, we need a template word document A containing all markup, tables, and the portion of text that will remain unchanged. In this template, you need to define the places where the values from the Excel workbook will be substituted - the most convenient way to do this is with the help of bookmarks. After that, you need to arrange the Excel data in such a way as to match the Word template, and last but not least, write the transfer procedure itself in VBA.
So, first things first.
Create a Word Document Template
Everything is extremely simple here - we create a regular document, type and format the text, in general, we achieve in order to get the necessary form. In those places where it will be necessary to substitute values from Excel, you need to create bookmarks. This is done in the following way:
Thus, you will need to create all bookmarks, that is, mark all the places where data from Excel will be inserted. The resulting file must be saved as a "MS Word Template" using the "File" -> "Save As..." menu item.
Excel data preparation
For convenience, I decided to put all the data that needs to be transferred to the Word document on a separate worksheet called Bookmarks - bookmarks. This sheet has two columns: the first contains the names of the bookmarks (exactly as they are named in the Word document), and the second contains the corresponding values to be transferred.
Some of these values are obtained directly from the data entry sheet, and some are from auxiliary tables located on the Support sheet. In this article, I will not analyze the formulas that calculate the desired values, if something is not clear, ask questions in the comments.
At this stage, it is important to correctly specify all the names of the bookmarks - the correctness of the data transfer depends on this.
Transfer procedure
But this is the most interesting. There are two options for executing the data migration code:
- The code is executed in an Excel workbook, the data is passed to Word one value at a time and is immediately placed in the document.
- The code is executed in a separate Word document, all data is transferred from Excel in one batch.
In terms of execution speed, especially when in large numbers bookmarks, the second option looks much more attractive, but requires more complex actions. That's the one I used.
Here is what needs to be done:
- Create a macro-enabled Word document template. This template will contain the executable VBA code.
Why is it necessary to create a template? In this way, you can protect the program code from unintentional changes. The fact is that every time you open a template, a new Word file is created, which simply cannot be saved over an existing template. The template itself remains unchanged.
- In the created template, you need to place a program written in VBA. To do this, when editing a template, press the Alt + F11 key combination and enter the program code in the Visual Basic editor window that opens.
- In an Excel workbook, write code that calls the fill procedure from the newly created Word template.
I will not provide the text of the procedure in the article - it can be easily viewed in the FillDocument.dotm file located in the Template folder in the example archive.
How can you use all this to solve your particular problem?
I understand that in words it all looks very simple, but what happens in practice? I suggest you just use the ready-made option. Download the archive with the example, in an Excel workbook, press the key combination Alt + F11 to open the Visual Basic editor and read all my comments on the program. In order to change the program to suit your needs, you only need to change the value of a few constants, they are placed at the very beginning of the program. You can freely copy the entire text of the program into your project.
Archive structure
The archive attached to this article contains several files.
The main file is an Excel workbook called "Generate Confirmations". There are 4 worksheets in this workbook, of which only two are displayed: "Input" - a data entry sheet and "Database" - an archive of all entered documents.
The Templates folder contains Word document templates. One of them is a template containing a bookmark filling program, and the second is a form to fill out. You can use the template with the program without changes, but the form to fill out, of course, will have to be altered in accordance with your needs.
How to rework the example "for yourself"?
- Prepare a Word document template to be completed. Create all the necessary bookmarks in it and save it as a "MS Word template".
- Copy the FillDocument.dotm file from the archive attached to this article to the folder with the prepared template. This file is responsible for filling in the template bookmarks, and nothing needs to be changed in it.
- Prepare an Excel workbook for data entry. It's up to you to decide whether it will have any "advanced" user interface and perform various tricky calculations. The main thing is that it contains a worksheet with a table of correspondence between the name of the bookmark in the Word template and the value to be substituted.
- Insert the VBA program code from the sample file into the prepared workbook. Replace all constants according to your project.
- Test for correct operation.
- Actively enjoy!
Once, after summing up the results of the Olympiad, when the participants needed to send out letters with the results of the passed (or not passed) subjects, I noticed that the girl was sitting and entering the exam results, the name of the student and other information into the letter template with her hands. In front of her lay several sheets printed from Excel with names and grades. Copy-Paste, Ctrl + C - Ctrl + V, transferring the full name from the nominative to the genitive case, and so work has already been done with half of the first of the three data sheets. How long do you think she could sit still copying the data, and then checking the result? I think for a long time, and there would be quite a few mistakes. But then she still had to sign postal envelopes ... I felt sorry for her time and in a few minutes I showed how this process could be automated using simple means. After a short excursion, her work was completed in 20 minutes.
In this lesson, I will show you how to quickly create documents (letters, invitations) with a minimum of time. In different versions of languages, this described process is called differently. So in Russian it is "Merge", and in English - "Merge".
I hope that "letters of happiness" from the pension fund, tax notices are created in the same way :)
Instruments
To create documents, we need OpenOffice Writer. In it we will create a letter template. You will also need OpenOffice Calc. In it, we will create a database with the names and addresses of those persons whom we, for example, want to invite to a party. Instead of programs from the OpenOffice package, MS Word and Excel can be easily used. The database can easily lie in MS Access. And so, let's get started.
Database creation
A database is nothing more than a table in MS Excel or OpenOffice Calc, where the data is arranged in columns and the first row is used to name the columns. Let's start, for example, the columns "name", "city", "zip code", "address". Enter the data and save the file to disk.
Other sources can be used as databases, for example The address book Outlook, or MS Access database.
Create a letter template
With the creation of a letter template, the situation is a little more complicated. Since the data from the table (our database) will be substituted into the template as is, then the letter must be written accordingly. If your full name is in the nominative case in your database, then you are unlikely to be able to use this field in the appeal “Dear,<ФИО>!" and for the envelope in the line "To:<ФИО>". In the latter case, the full name will look somewhat clumsy.
When composing a letter template, I recommend starting with writing a letter to some real person. You can to yourself. Then highlight in it the data that you will take from the database and replace them with the appropriate fields. Before replacing the selected text with a field, I recommend that you pay attention to what letter the field in the database should begin with (lowercase or uppercase). Of course, you can create a template immediately with the fields, but then you may not notice some blunders, such as inconsistencies in cases.
Already in the above template, it is clear that we need a full name in the genitive case and a field containing only the first and middle name. With the first field, we can replace “To” with “Recipient” and then the full name in the nominative case will suit us perfectly. With the second field, everything is somewhat more complicated and we will have to create another column in the database and fill it with the appropriate data. In one of the following lessons I will tell you how to do this automatically, but for now we will assume that we already have such a field.
To insert a field and bind the template to the database, you must perform the following sequence of actions. Select a database in OpenOffice Writer
and click "Define".
Then insert the field from the connected database in the right place. To do this, press Ctrl + F2, or select "Fields" and "Other" from the "Insert" menu. In the window that opens, select the "Databases" tab, select "Mail Merge fields" in the field type, select the appropriate field from the database and click "Insert". The database itself can also be selected in this window.
After one field is inserted, without closing the current window, select the text to be replaced by the next field, select the field and click "Insert" again. And so for each of the fields.
For MS Word, the situation is somewhat similar and perhaps more convenient. After creating a table in MS Excel, launch MS Word and go to the "Mailouts" tab. On this tab, you can see the three necessary steps: "Start the merge", "Compose the document and insert fields" and "Complete". There is also an intermediate step "View result", but it is optional.
And so the work begins with the selection of a document. It can be letters, stickers, a regular Word document. You can also run the merge wizard right here, which will guide you through all the steps in the process. The next step is to select the recipient, that is, the database. Here you can choose a ready-made database (for example, the MS Excel table created at the previous stage), or create a new list. After the list is selected, the buttons “Insert merge field”, “Edit recipient list”, etc. become active. Through the dialog “Edit recipient list”, which opens with the corresponding button, you can select from the entire list only the records necessary for merging.
 |
 |
When inserting fields, I find it convenient to highlight all fields in gray by clicking on the "Select Merge Fields" button. Otherwise, if there are many such fields, then they can be lost in the text.
And so, the template is ready.
Letter generation
The last stage is the generation of letters. In OpenOffice, for this, in the "Tools" menu, select "Mail Merge Wizard ..." and go through all the proposed steps to combine our template with the database.
 |
 |
In the dialog box, we see that out of the proposed 8 points, we do not have to perform some points. So we have already created the document and we do not need to edit it, we have also already inserted the address block. But let's go in order.
First, we choose that we create letters based on the current document and click "Next" at the bottom of the window. The second point is whether we will have this paper letter or e-mail. In order for the letter to be saved to a file, select the first one. Click "Next". In the third paragraph, we are offered to create an address block, but since we created it by hand, we uncheck all the boxes and proceed to the next step. Here we are offered to insert an appeal, but we also already have it, so click "Next". In the sixth paragraph, you can edit the document, look at the document with the fields inserted from the database and, if necessary, exclude some fields.
At step 7, it is proposed to edit each of the files created as a result of connecting the template to the database. And finally, at the last, step 8, you can choose what to do with the received documents. You can save, print or send by e-mail. In this case, you can save all documents in one file, or each document separately.
In MS Word, the merging process is completed by clicking the "Find and Merge" button.
From the drop-down menu, it is obvious that all letters can be printed and sent via e-mail, but it is not obvious that all of them can be saved to a file. To save, select the first item - "Change individual documents". This will open a new document, in which each new letter will be located with new page, and here you can already save this file.
Conclusion
And so, in this lesson, you learned how to use the merge tool to create documents based on a template. Let's briefly write down the sequence of actions:
- It is necessary to create a database in the spreadsheet processor, from which data will be taken to fill in the fields.
- You need to create a letter template.
- Perform the merge and save the result.
A, last names in column B and professions in the column C.
2. Create a word document (.doc or .docx)
(A), (B) and (C).
(A), (B) and (C) (A)- name, (B)- surname, (C)- profession.
Settings programs.
3. Choose paths for files and folders
Select
4. Set the sheets and rows of the desired data
Excel file data sheets
Excel file data rows Excel file data sheets
1 .
If you want all sheets and/or rows of your excel file with data to participate in the formation of the document, click on the corresponding button on the right with the caption numbers(its inscription will change to All).
5. Set the name template for new word files
Set the name template for new word files:
New word files names template is a template for the names of new documents (word-files) generated by the program. Here, the name template contains the names of the columns of the excel file, framed by curly braces: (A) and (B). When generating a new document, the program will replace all (A) and (B) the corresponding cell values from the excel file - this will be the name of the new document (word file).
You can set your own border characters on the tab Settings programs.
6. Click "Generate"
Click the button Generate and the progress will appear on the screen. Documents (word-files) will be created exactly as many lines of the excel-file are involved in the formation.
7. Everything
All documents (word-files) have been created and are located in the folder specified in Folder to save the new word files. Everything:)
Exwog- template report generator from excel to word
Free Word file generator by template (Word file) based on Excel file data
Works on Mac OS, Windows and Linux
Allows you to set the names of new generated word files
Allows you to set sheets and rows of the required data
Allows you to set border characters for Excel column names
Easy to use
Store your data in Excel format (.xls and .xlsx) and generate Word files (.doc and .docx) in a few clicks :)
How it works?
Take a look at your excel file
In this example, the excel file contains information about clients. Each line corresponds to a specific client. Names are in a column A, last names in column B and professions in the column C.
Click to view
Create a word document (.doc or .docx)
Click to view
Create a "template" (word-file) for generating new documents (word-files). Here the text of the "template" contains the names of the columns of the excel file, framed by curly braces: (A), (B) and (C).
The program will generate new documents according to the "template" replacing all (A), (B) and (C) corresponding cell values from the excel file: (A)- name, (B)- surname, (C)- profession.
You can also set your own border characters on the tab Settings programs.
Choose paths for files and folders
Select paths for files and folders (buttons labeled Select). In the program, you specify the following paths:
Excel file with data (*.xls, *.xlsx)- this is the path to your excel file with data (customer information);
Word template file (*.doc, *.docx) is the path to your "template" (the word file created in the previous step);
Folder to save the new word files- this is the path to the folder where the program will save the newly generated documents.
Click to view
Set the sheets and rows of the desired data
Click to view