Basically we have to make meeting agendas for our conferences (where many firms and attend) and it's a round table discussion in which one firm is addressed at a time. The general layout is:
1.) Firm 1 - Representative
bullet point
bullet point
bullet point
2.) Firm 2 - Representative
bullet point
bullet point
bullet point
3.) Firm 3 - Representative
etc. etc.My company has to make these frequently and the Firm and representatives are all held in excel files. Is there a way to quickly generate this document?
34 Answers
Yes, you can accomplish this using mail merge.
Open your agenda template and go to the Mailings ribbon. Go to "Select Recipients" and choose "Use an Existing List" from the drop down menu. Once that is linked, you can insert fields in the document. Go to "Insert Merge Field" to select the fields to insert. Once you're finished, go to "Preview Results" to review the document. You'll want to select the correct record at this time, you can either jump through the records using the arrows to find it or go to "Edit Recipient List" for a custom list. After that, go to "Finish & Merge" and select "Edit Individual Documents" and select the choice for "Current Record" (if you have multiple, I don't recommend doing them all together because you'll still have to separate them into individual files). This will create the document and all you need to do is save it.
I would like to suggest two possible solutions, 1st is Non-VBA and another is VBA (Macro).
Method 1 Non VBA:
- Create a single Sheet Workbook or better enter your meeting data in form of TABLE in Excel.
- Open blank file in Word.
- From Insert Tab, select Object from the Drop down button.
- Then click Create from File tab, and Browse to the Workbook you wish to insert.
- Next is, to choose whether you wish the object to be linked or not.
- Finish with Ok.
Method 2 VBA (Macro):
Note:
- This needs a TABLE in Excel (better work with single Sheet file).
- In VB editor click Tools then References and add this Microsoft Word 12.0 Object Library, finish with Ok.
Create Command Button in Excel Sheet and Copy & Paste this code for it.
Sub CommandButton1_click() Dim tbl As Excel.Range Dim WordApp As Word.Application Dim myDoc As Word.Document Dim WordTable As Word.Table Application.ScreenUpdating = False Application.EnableEvents = False Set tbl = ThisWorkbook.Worksheets(Sheet1.Name).ListObjects("Table1").Range On Error Resume Next Set WordApp = GetObject(class:="Word.Application") Err.Clear If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application") If Err.Number = 429 Then MsgBox "MS-Word not found, aborting now !!." GoTo EndRoutine End If On Error GoTo 0 WordApp.Visible = True WordApp.Activate Set myDoc = WordApp.Documents.Add tbl.Copy myDoc.Paragraphs(1).Range.PasteExcelTable _ LinkedToExcel:=False, _ WordFormatting:=False, _ RTF:=False Set WordTable = myDoc.Tables(1) WordTable.AutoFitBehavior (wdAutoFitWindow) EndRoutine: Application.ScreenUpdating = True Application.EnableEvents = True Application.CutCopyMode = False End Sub
N.B. In this code Sheet & Table Names are editable.
I found python-docx-template quite interesting:
Example .docx document:
My house is located
{%- if living_in_town -%} in urban area
{%- else -%} in countryside
{%- endif -%} and I love it.from docxtpl import DocxTemplate
doc = DocxTemplate("my_word_template.docx")
context = { 'company_name' : "World company" }
doc.render(context)
doc.save("generated_doc.docx")For reading the data from excel see .
Other alternative:
- Python mailmerge: -->
I've created this free tool to generate word documents from excel data , word template must have placeholders with the columns names as follow ${col_name} Might be useful !
