Open multiple copies of Word template document using VBA for applications

Question

I am developing a database application where users open a Word template document and merge this with database records. I have successfully written code that does this for one record where I open a template file and replace items in the template document with DB data. I have tried opening the template document multiple times (once for each DB record), but this opens a Word dialogue box prompting the user to open the second and subsequent documents as read only (not very elegant) and I get a Word Normal.dotm error when each of the documents is closed. So, how can I use one template document to create multiple word documents at the same time. Two options as I see them are to 1) Save one doc in a new name before creating other documents or 2) Have one document with multiple pages (one per DB record) but to do this I would have to copy and paste the template document contents once for every record, but I don't see how to do this. Please keep in mind that I am experienced in database programming and only a rudimentary knowledge of VB for applications, so the more explicit you can be, the more useful it will be.

Thanks in advance.

Based on the dialog box you describe, it sounds like you are opening the Word template, instead of using it as a basis to create a new document. Can you post the code you are using to open the template? — dendarii

Steve Mallory Steve Mallory · Accepted Answer · 2011-05-17T12:56:40

This function will create a new document for each record based on a specified template. I'm using a preset bookmark in the Word template to add data from the database. This sub would run in the database.

Public Sub CreateDocs()

    Dim oWord As Word.Application
    Set oWord = New Word.Application
    Dim oDocument As Word.Document
    Dim oDatabase As DAO.Database
    Set oDatabase = CurrentDb

    ' where to save our files
    Dim strDocPath As String
    strDocPath = CurrentProject.Path & "\"

    ' preset bookmark in template
    Dim oBookMark As Word.Bookmark

    ' query with records
    Dim oRecordset As DAO.Recordset
    Set oRecordset = oDatabase.OpenRecordset("qrySomeQuery")

    ' template file
    Dim strTemplateName As String
    strTemplateName = "C:\users\you\Documents\Doc1.dotx"

    ' hide Word
    oWord.WindowState = wdWindowStateMinimize
    oWord.Visible = False

    While Not oRecordset.EOF
        ' create new document from template
        Set oDocument = oWord.Documents.Add(strTemplatePath, False, , False)

        ' get reference to bookmark in template
        Set oBookMark = oDocument.Bookmarks("bkmkSomePlace")

        ' add our data
        oBookMark.Range.Text = oRecordset.Fields(0).Value & vbTab & oRecordset.Fields(1).Value

        ' Save and close
        ' Saving using one of the query fields that is unique
        oDocument.SaveAs strDocPath & "generatedFile" & oRecordset.Fields(0).Value, wdFormatDocumentDefault
        oDocument.Close

        ' next record
        oRecordset.MoveNext
    Wend

    oWord.Close
    Set oWord = Nothing
    If Not oRecordset Is Nothing Then oRecordset.Close
    Set oRecordset = Nothing
    oDatabase.Close
    Set oDatabase = Nothing
    Set oDocument = Nothing
End Sub

Open multiple copies of Word template document using VBA for applications

1 Answers