Create a word doc from data in excel one doc per row / record

[ideasfactory]

Hi,

I want Excel to create a word doc based on a .dot that is populated with data from excel, most importantly I need one word doc per row or record from excel.

I have 4 columns in excel:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Title
[/TD]
[TD]Name
[/TD]
[TD]Address
[/TD]
[/TR]
[TR]
[TD]FY1718-824
[/TD]
[TD]Initiative Name1
[/TD]
[TD]Lisa
[/TD]
[TD]Address1
[/TD]
[/TR]
[TR]
[TD]FY1718-627
[/TD]
[TD]Initiative Name2
[/TD]
[TD]Craig
[/TD]
[TD]Address2
[/TD]
[/TR]
[TR]
[TD]FY1617-345
[/TD]
[TD]Initiative Name3
[/TD]
[TD]Ben
[/TD]
[TD]Address3
[/TD]
[/TR]
[TR]
[TD]FY1617-450
[/TD]
[TD]Initiative Name4
[/TD]
[TD]Elizabeth
[/TD]
[TD]Address4
[/TD]
[/TR]
</tbody>[/TABLE]

I have a word .dot that has areas throughout the document where I want to populate the 4 columns of data above. For example on page 1 I want ID in a specific location, on page 2 I want Title and Name and on Page 3 I want Address.

I want to save a word document .doc with the ID and Title as the filename.

The end result in this example would be four word .doc with page 1 ID in a specific location, on page 2 Title and Name and on Page 3 Address.

Does anyone know how to achieve this with VBA? I have investigated Mail Merge but does not create separate word docs only creates them in the single document.

Thanks

 

[Trevor G]

In the .dot document you would need to use BookMarks and give them names that you can then automate each record in excel to produce separate documents. If you create the BookMarks then list them in your thread it will help getting a result for you.

In the .Dot Word document place your cursor in a position like for ID then use the Insert Tab and Bookmark create a Bookmark like ID then repeat this for the Others that you want to use.

 

[ideasfactory]

Hi,

Thanks for the response, I have inserted the following bookmarks into template.dot which is in the same folder as the excel file data.xlsm:

ID
Title
Name
Address

It would be really appreciated if you could post the excel VBA to:

1. Create a word document (.docx) per row of data using the template.dot and save the .docx in the current directory of the excel file data.xlsm. The filename constructed from the ID and Title for example first record word document title would be 'FY1718-824 Initiative Name1.docx' etc

2. In the word document (not .dot) populate the bookmarks listed above for each row of data.

3. Save word file if first time of creating file, replace if not first time of creating file

4. Repeat from step 1 until all records in excel have a .docx word document populated with updated bookmark text to match that in excel

Let me know if you need any more info.

Cheers

 

[Trevor G]

Take a look at this code to see if it helps. I have added comments to help. Please read the comments and adjust the File Path and also the Word Template name

Option Explicit
'Set the References to use Word.Object XX Library
'In VBA Tools Menu ----> References ----> Search down for Word
'Adjust the Path to the locaction you need
Const FilePath As String = "C:\Path name here"
Dim wdApp As New Word.Application
Dim PerCell As Range

Sub AddDocs()
'create Word
Dim wdDoc As Word.Document
wdApp.Visible = True
Dim PerRan As Range
'create a reference to all the people
Range("A1").Select
Set PerRan = Range(ActiveCell, ActiveCell.End(xlDown))
'Create document for each person
For Each PersonCell In PerRan
    'open a document in Word
    Set wdDoc = wdApp.Documents.Open(FilePath & "Word template name.dot")
    'go to each bookmark and add the data
    copycell "ID", 1
    copycell "Title", 2
    copycell "Name", 3
    copycell "Address", 4
    'save the doc and close it
    wdDoc.SaveAs2 FilePath & "person " & PerCell.Value & ".docx"
    wdDoc.Close
    'Now create the next one
Next PerCell
wd.Quit
MsgBox "Your files have been created " & FilePath
End Sub

 

[ideasfactory]

Hi,

Thanks but the code does not work:

1. copycell throws and error sub or function not defined, I cannot find any reference to what copycell is
2. Where did PersonCell come from?
3. This is nothing to do with people.
4. Percell and Perran?

I am not a VBA coder it is very difficult for me to troubleshoot code this is incorrect.

Sorry this has not helped at all.

 

[Macropod]

Does anyone know how to achieve this with VBA? I have investigated Mail Merge but does not create separate word docs only creates them in the single document.

It does if you drive the merge with a macro such as the one listed under Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks thread at:
Mailmerge Tips & Tricks
or:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge

 

[ideasfactory]

Hi,

Very detailed post, thanks I think the bookmarks in Word is a better way to go but the code does not work.

 

[Macropod]

I think the bookmarks in Word is a better way to go but the code does not work.

Did you at least try the code in the link I posted with a mailmerge main document?

As for "the code does not work", with reference to post #4, that's hardly surprising as it's incomplete and erroneous: it references a copycell sub/function that is missing; and references a mis-named variable (Percell) instead of PersonCell.
Your comments re PersonCell and Perran suggest you really haven't understood the code's basics and that you've misinterpreted variable names as necessarily implying something about the data.

 

[ideasfactory]

Hi,

I have got the code below to just work but no where near as elegant as it should be, there is no error handling if files are already created and to overwrite them. The code creates 5 word files when it should be 4, the first a blank word file called ID then it creates the 4 word documents after. The title of the word docx should be constructed as ID & Title for example FY1718-824 Initiative1.docx etc.

Can anyone help tidy up this code to make it work properly.

Option Explicit

'change this to where your files are stored

Const FilePath As String = "C:\Users\pablo.DESKTOP-EE3UNF2\Desktop\Excel to Word"

Dim wd As New Word.Application

Dim SOPCell As Range

Sub CreateWordDocuments()

    'create copy of Word in memory

    Dim doc As Word.Document
    wd.Visible = True
    Dim SOPRange As Range
    'create a reference to all rows of data

    Set SOPRange = Range(Range("A1"), Range("A1").End(xlDown)).Cells

    'for each row in the list

    For Each SOPCell In SOPRange
        'open a document in Word
        Set doc = wd.Documents.Open(FilePath & "test.docx")
        'go to each bookmark and type in details
        CopyCell SOPCell, "ID", 0
        CopyCell SOPCell, "Title", 1
        CopyCell SOPCell, "Name", 2
        CopyCell SOPCell, "Address", 3
        'save and close this document
        doc.SaveAs2 FilePath & SOPCell.Value & ".docx"

        doc.Close

    Next SOPCell

    wd.Quit

    MsgBox "Created files in " & FilePath & "!"

End Sub

Sub CopyCell(rg As Range, BookMarkName As String, ColumnOffset As Integer)

    'copy each cell to relevant Word bookmark

    wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName

    wd.Selection.TypeText rg.Offset(0, ColumnOffset).Value

End Sub

 

[ideasfactory]

Hi,

there is no error handling if files are already created and to overwrite them.

When I run the code the first time it runs okay but second time I get a runtime 462 error remote server machine does not exist or is unavailable and when I select debug it takes me to the following code: wd.Visible = True