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

 

[ideasfactory]

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]

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]

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]

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]

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.