I have a word file with general instructions for a project and a list of
names in an Excel file. There are 900 names in the list. I need to have
900 identical Word documents each with a name from the list. All documents
will be saved in the same folder. Eventually information specific to each
name will be entered in that file via a hyperlink in Excel.
I've created all of the hyperlinks.
Can I automate File Save as to save the word document with each of the names
from the excel file?
This is possible, although the request would be more on-topic in one
of the VBA newsgroups such as microsoft.public.word.vba.general.
A good way to handle this is by DAO (Data Access Objects). The
following is based loosely on the code in
http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm.
In your Excel worksheet, the first row should be a heading row. I'll
assume the column of names has the heading "Name". Select all the
nonempty cells in that column, go to Insert > Name > Define, and
accept the proposed name of the range, which is the heading "Name".
(If you want to call the range something else, you'll have to change
the constant NamedRange in the macro to match.) Save the worksheet.
Now you can close it; it doesn't have to be open to run the macro.
In Word, open your instruction document and save it as a template (in
the File > Save As dialog, set the "Save as type" box to "Document
Template").
Open the Visual Basic editor. Select the template's name in the
Project pane and click Insert > Module. Go to Tools > References and
put a check mark next to "Microsoft DAO 3.6 Object Library". Copy the
macro code from this post and paste it into the editing space.
Change the NameTemplate to the name of the template, change the
SavePath to the name of the folder where the documents should be
saved, and change the NameSheet to the path\filename of the Excel
file. Save the template.
Finally, press F5 to run the macro, and wait until it finishes.
Note: Obviously I haven't tested this with 900 file names, so I can't
guarantee that it won't crash or hang with some kind of memory
problem. If it does, you could try splitting the list into two or more
Excel files and running the macro on each of them separately. Good
luck!
'------ code starts here ------------------
Sub MakeNameDocs()
Dim oDoc As Word.Document
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
Dim NameList As Variant
Dim strFN As String
Dim DocNum As Long
Const NameTemplate = "namesbase.dot"
Const SavePath = "c:\names\"
Const NameSheet = "c:\names\names.xls"
Const NamedRange = "Name"
On Error GoTo ErrHdl
' Open the database
Set db = OpenDatabase(NameSheet, _
False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset( _
"SELECT * FROM `" & NamedRange & "`")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
NameList = rs.GetRows(NoOfRecords)
For DocNum = 0 To NoOfRecords - 1
strFN = SavePath & _
NameList(0, DocNum) & ".doc"
Set oDoc = Documents.Add( _
Template:=NameTemplate, Visible:=False)
oDoc.SaveAs FileName:=strFN
oDoc.Close
Next
Set oDoc = Nothing
Set rs = Nothing
Set db = Nothing
MsgBox "Done! Created " & NoOfRecords _
& " files in " & SavePath
Exit Sub
ErrHdl:
MsgBox "Error " & Err.Number & _
vbCr & Err.Description
Set oDoc = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
'------ code ends here ------------------
--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
