Macro to fill word template

tr1face · Aug 18th 2021

Hi Experts,

I hope someone can help creating a macro that would fill my word templates based off an excel template since it's a very repetitive and time consuming task.

Attached you will find everything needed.

Templates are located under Input folder and are completed based of "Data" sheet.

There are 2 templates, macro needs to ask before running which template should be used ( Do you need Logo?, Yes, No) and use accordingly.

Loops for each number and creates individual word documents based of number from column A and saves them under Output folder after each section was completed.

Word documents resulted should be saved as concatenate between columns B, C, & "CV" (LAST NAME, FIRST NAME, "CV", "with Logo/without Logo"). Last part depending on which template was used.

Word document may have up to 3 or 4 items in the same section for some No.: (see yellow highlights)

These need to be aligned.

Rows may be inserted if necessary, but template should be under <1 page.

Thank you for your time in advance, I hope this is something not too hard!!!

**royUK** · Aug 18th 2021

Why don't you do everything in Excel? It would be much simpler.

You can easily set up sheets to represent the Word Documents in Excel sheets.

tr1face · Aug 18th 2021

Hey Roy,

Thank you for your question.

The resulting documents are always word files since they go into a different process, and they should be keeping that format (centered for 1 page, etc) while they should all keep the same width.

And honestly, I was not aware they could be saved as word docs from excel

If the end result it's the same, I guess I can give it a try

**royUK** · Aug 19th 2021

I didn't say they could be saved a Word Documents it would be a lot simpler to have everything in Excel.

To code it for each document in Excel would be a big task. The only way I know is to create BookMarks in each Word Doc and matching Named Ranges in the Excel file.

tr1face · Aug 19th 2021

Ugh, I was hoping this is something simple since on the word templates the ranges are always the same or someone already developed something similar and some parts of the code can be included

I have been searching for a while now

tr1face · Aug 21st 2021

+Up

**royUK** · Aug 22nd 2021

This is some codethat will copy from named ranges in Excel to BookMarks in a Word Document.

The names must match.

Code

Sub XlNamesToWdBookmarks()
    Dim objWord As Object, docWord As Object
    Dim wb As Excel.Workbook
    Dim xlName As Excel.Name
    Dim Path As String
     
    Set wb = ActiveWorkbook
    Path = wb.Path & "\MyForm.dot" 'change the name
     
    On Error GoTo ErrorHandler
     
     'Create a new Word Session
    Set objWord = CreateObject("Word.Application")
     
    On Error GoTo ErrorHandler
     
     'Open document in word
    Set docWord = objWord.Documents.Add(Path)
     
     'Loop through names in the activeworkbook
    For Each xlName In wb.Names
         'if xlName's name is existing in document then put the value in place of the bookmark
        If docWord.Bookmarks.Exists(xlName.Name) Then
            docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
        End If
    Next xlName
     
     'Activate word and display document
    With objWord
        .Visible = True
        .ActiveWindow.WindowState = 0
        .Activate
    End With
     
     'Release the Word object to save memory and exit macro
ErrorExit:
    Set objWord = Nothing
    Exit Sub
     
     'Error Handling routine
ErrorHandler:
    If Err Then
        MsgBox "Error No: " & Err.Number & "; There is a problem"
        If Not objWord Is Nothing Then
            objWord.Quit False
        End If
        Resume ErrorExit
    End If
End Sub

Display More

tr1face · Aug 22nd 2021

Hey Ron,

Thank you for your time, your code it's elegant and after defining names & bookmarks copies them as intended

Can it be made to loop for each No.1 provided in column A, opening a fresh template when the number changes? It would be extremely helpful as I usually have to do a list of them

**royUK** · Aug 22nd 2021

Can you attach your latest workbook

tr1face · Aug 22nd 2021

Attached

I have defined names only for 1 row while testing, if the loop is possible should I enlarge the defined names range after? Will this result into a mismatch?

tr1face · Aug 23rd 2021

Quote from tr1face

Can it be made to loop for each No.1 provided in column A, opening a fresh template when the number changes? It would be extremely helpful as I usually have to do a list of them

Sorry, can it be made to loop for each number, not each No.1***

**royUK** · Aug 23rd 2021

I can't see what your codee is doing.

There's no sample data or a Loop.

tr1face · Aug 23rd 2021

Hey Ron,

it's doing nothing right now, i only defined names in excel & bookmarks in my word template.

Can we add something to the previous code in order to loop/autofilter & create individual word files for each number in column A? Right now it would do the task, but just for 1 at the time

**royUK** · Aug 23rd 2021

There's no data so I'm not sure what you need.

Why do you need AutoFilter? Does the column contain unique numbers or are there duplicates?

tr1face · Aug 23rd 2021

Column A will have only unique numbers starting from 1

tr1face · Aug 23rd 2021

you can fill any dummies in the excel file, while numbering the rows in column A

**royUK** · Aug 23rd 2021

I don't know whether column A contains unique numbers or whether it needs filtering by number

tr1face · Aug 23rd 2021

Hey Ron,

Attached word and excel with some raw data. Column A will have unique numbers, so it can create a word template for each row

**royUK** · Aug 23rd 2021

Actually now I see what you are doing, it won't work that way.

You will need n extra step. The macro will require a sheet to copy each row to using VLOOKUP this can then be transferred. I'll add a sheet to your demo.

**royUK** · Aug 23rd 2021

I've added a sheet for the code to use.

It's important that your data contains no unused numbers, i.e. no empty rows.

I've converted the data to n Excel Table which makes it easier to use with formulas because a Table will expand as data is added so you don't need to change the formulas. Read the linked article, you will find it useful.

Overview of Excel Tables

The code will put each ID number into the Transfer sheet which will be used by VLOOKUP to populate the transfer row.

I haven't tested it so get back and let me know if you have any problems

Macro to fill word template

Participate now!

Share

Tags

Users Viewing This Thread