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



## ideasfactory (Jan 22, 2017)

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:


ID
Title
Name
Address
FY1718-824
Initiative Name1
Lisa
Address1
FY1718-627
Initiative Name2
Craig
Address2
FY1617-345
Initiative Name3
Ben
Address3
FY1617-450
Initiative Name4
Elizabeth
Address4


<tbody>

</tbody>
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 (Jan 22, 2017)

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 (Jan 23, 2017)

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 (Jan 23, 2017)

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 (Jan 24, 2017)

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 (Jan 24, 2017)

ideasfactory said:


> 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 (Jan 24, 2017)

Hi,

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


----------



## Macropod (Jan 24, 2017)

ideasfactory said:


> 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 (Jan 24, 2017)

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 (Jan 24, 2017)

ideasfactory said:


> 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


----------



## ideasfactory (Jan 22, 2017)

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:


ID
Title
Name
Address
FY1718-824
Initiative Name1
Lisa
Address1
FY1718-627
Initiative Name2
Craig
Address2
FY1617-345
Initiative Name3
Ben
Address3
FY1617-450
Initiative Name4
Elizabeth
Address4


<tbody>

</tbody>
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


----------



## ideasfactory (Jan 24, 2017)

Macropod said:


> 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.




Hi,

Yes I did have a quick go at it but I do not know where to paste the code, excel or word and when I do a mail merge I used the wizard so not sure how to interact to get the macro to run with the wizard so hit a brick wall at that point.

I would like to round out the bookmark method and get the runtime error working and that it only creates word files for the data and title of the file is based on ID and Title values.

Any help would be appreciated.


----------



## Macropod (Jan 24, 2017)

ideasfactory said:


> 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.


That's probably because your worksheet has a header row titled 'ID' and your SOPRange is defined as including that row.


----------



## Macropod (Jan 24, 2017)

ideasfactory said:


> I do not know where to paste the code, excel or word and when I do a mail merge I used the wizard so not sure how to interact to get the macro to run with the wizard so hit a brick wall at that point.


The fact it's a Word macro should give you a pretty good idea. As such it doesn't need to know whether the data source is an Excel workbook, Access database, Text file, Word table, CSV file or any other valid mailmerge data source. All you'd use the wizard for is the mailmerge setup. You'd then run the macro exactly the same way you would with Excel.


----------



## ideasfactory (Jan 24, 2017)

Macropod said:


> That's probably because your worksheet has a header row titled 'ID' and your SOPRange is defined as including that row.



Great thanks I have started from A2 not A1 that has fixed that issue. Do you have any idea what the runtime error 462 is to do with wd.Visible = True ?  

I have all this code in a module is this in the right place?

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("A2"), Range("A2").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
        MsgBox "Created files: " & SOPCell.Value & ".docx"


    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 (Jan 24, 2017)

Macropod said:


> The fact it's a Word macro should give you a pretty good idea. As such it doesn't need to know whether the data source is an Excel workbook, Access database, Text file, Word table, CSV file or any other valid mailmerge data source. All you'd use the wizard for is the mailmerge setup. You'd then run the macro exactly the same way you would with Excel.



Sorry I am a beginner so you will need to bear with me, okay I will add the code to Word then use the wizard to get to the last part before printing and run the macro to see what happens.


----------

