creating multiple documents automatically from list

[elijahkaufman]

Hi I have a list of 850 names and I have a report card template. I
need to create a report card for all 850 students with the students'
name as the filename and one of the fields inside the report card
filled in with the students' name. Is there any way in Word to do this
automatically? Is there another program that could do this or
somewhere else I should post?
thanks

 

[Lynn]

Hi - you can use a macro using VBA to do this in Word.
I am assuming your Report Card Template is set up as a Word Form? If
it is, open the properties of the Student Name field and type the words
"StudentName" in the bookmark name box. Protect the form and close and
save the template.

You should list all your 850 names in a word document in a table with
one column and no header row. The full name should be typed in the one
table cell (one name per row).

In case you do not know how to open the VB Editor here is the basics -
if you already know how to do this then just ignore it:
Click on Tools, Macros, VB Editor
In the VB Editor window, if you do not see a project explorer list on
the left, click on View, Project Explorer. You should be able to see
the name of the document with the table in it on the Project List.
Double Click it and expand the folder Microsoft Word Objects. Double
click the This Document item. A whie space will become available on
the right hand side.

Copy this code to the white space

What this code is doing is:
It scrolls through every row in the table, picks up the text from each
row, removes the end of row and end of cell markers, then stuffs the
text inside a string variable called sName. It creates a document
based on your reportcard template, adds the sName variable to the
StudentName field, saves the document as, using the sName variable as
the document name and closes the document.

I don't know what name you gave your report card template, in the code
below I have named it ReportCard.dot. Change it to match whatever you
named your template.

To run the macro, you can either run it from the VB Editor by clicking
on the Run button on the toolbar (a right facing arror) or press F5.
Alternatively you can run the macro from word, by either clicking on
tools, macro, Macros. In the Macros dialog box, click on the drop down
"Macros In" and choose the document name (the one with the table in
it) and the macro "CreateandSaveReport" will appear in the macro list.
Click on Run.

Sub CreateandSaveReport()

Dim Source As Document
Dim iCounter As Integer
Dim objRow As Row
Dim sName As String
Dim rngRange As Range

Set Source = ActiveDocument
iCounter = 1
For Each objRow In Source.Tables(1).Rows
Set rngRange = Source.Tables(1).Rows(iCounter).Range
rngRange.End = rngRange.End - 2
sName = rngRange
Documents.Add "ReportCard.dot"
ActiveDocument.FormFields("StudentName").Result = sName
ActiveDocument.SaveAs "c:\My Documents\" & sName
ActiveDocument.Close SaveChanges:=wdSaveChanges
iCounter = iCounter + 1
Next
End Sub

Not sure how long this macro will take to run with 850 names! Hope it
is OK. One other thing. I have put the folder name c:\My Documents as
the folder name to save the reports to. If you want them to save to
another folder under My Documents then just add the folder name like
this c:\My Docments\Folder Name.

Regards Lynn

 

[ducksredux]

Thank you so much, Lynn! That's so helpful.
Is it a simple thing to add a couple more fields, like one for the
teacher's name and one for the grade level?
Eli

 

[Lynn]

Yep - I have tidied up the code a bit and have reinserted it in this
post. You will need to add the fields to the ReportCard template, in
the field properties overwrite the suggested bookmark with Teacher and
Grade respectively. You will also need to add another two columns to
your table, one for the teachers name and one for the grade.

With the code I have just reset the range to fill the string variables
with cell 1, cell 2 and cell 3 text

Dim Source As Document
Dim iCounter As Integer
Dim objRow As Row
Dim objTable As Table
Dim sName As String
Dim rngRange As Range


Set objTable = ActiveDocument.Tables(1)
iCounter = 1
For Each objRow In objTable.Rows
Set rngRange = objTable.Rows(iCounter).Cells(1).Range
rngRange.End = rngRange.End - 1
sName = rngRange
Set rngRange = objTable.Rows(iCounter).Cells(2).Range
rngRange.End = rngRange.End - 1
sTeacher = rngRange
Set rngRange = objTable.Rows(iCounter).Cells(3).Range
rngRange.End = rngRange.End - 1
sGrade = rngRange
Documents.Add "ReportCard.dot"
With ActiveDocument
.FormFields("StudentName").Result = sName
.FormFields("Teacher").Result = sTeacher
.FormFields("Grade").Result = sGrade
.SaveAs "h:\My Documents\" & sName
.Close SaveChanges:=wdSaveChanges
End With
iCounter = iCounter + 1
Next
End Sub
Regards
Lynn

 

[ducksredux]

Thanks so much Lynn. We ran the code today and it worked perfectly!
Thanks for saving us 8 hours of tedious data entry drudgery.
-Elijah

 

[Lynn]

my pleasure - I love it when a plan comes together