Create Word documents from templates in background

I have a database with information for jobs. I have included buttons in the form to create/open Word documents using templates and load data from the DB to the doc. These work perfectly.

Now I'm trying to create a button which will create all the Word Documents using the specified templates and load the necessary information into them. That way the user doesn't have to create every single file manually. He will just have to click this new button to create all the files at once. All the templates use the .dot extension. This is the function that I use to create/open a single document:

Private Function createFile_ClientInformation(action As Integer)

On Error GoTo Err_createFile_ClientInformation

    Dim wd_app As Word.Application
    Dim path As String
    Dim theTemplate As String
    Dim fs As Object
    Dim current As Object
    Dim address As String
    path = "C:\Inetpub\private\" & Me.jobName.Value & "\"
    theTemplate = "C:\Inetpub\private\templates\Client"

    Set wd_app = New Word.Application
    If Dir(path, vbDirectory) = "" Then
       Set fs = CreateObject("Scripting.FileSystemObject")
       fs.createfolder (path)
    End If

    If Dir(path & "Client Information.doc") = "" Then
       wd_app.Documents.Add Template:=theTemplate
       wd_app.ActiveDocument.SaveAs path & "Client Information.doc"
       If Not (IsFileOpen(path & "Client Information.doc")) Then
          wd_app.Documents.Open filename:=path & "Client Information.doc", ReadOnly:=False
          MsgBox "An error occurred while trying to open the file. It may be opened by another user. Please try again later."
          Exit Function
       End If
    End If
    wd_app.Visible = True
    Set current = wd_app.ActiveDocument
    If IsNull(Me.contactPerson) Then
       current.FormFields("jobName").result = Nz(Me.jobName, "Job Name is Required")
       current.FormFields("contactPerson2").result = Nz(Me.jobName, "Contact Person is Required")
       current.FormFields("contactPerson").result = Nz(Me.jobName, "Contact Person is Required")
       current.FormFields("contactPerson").result = Nz(Me.contactPerson, "Name of Contact Person is Required")
       current.FormFields("jobName").result = Nz(Me.jobName, "Job Name is Required")
       current.FormFields("contactPerson2").result = Nz(Me.contactPerson, "Name of Contact Person is Required")
    End If
    current.FormFields("phone").result = Nz(Me.phoneNumber, "")
    current.FormFields("fax").result = Nz(Me.faxNumber, "")
    address = Nz(Me.streetAddress, "") & " " & Nz(, "") & ", " & Nz(UCase(Me.state), "") & " " & Nz(Me.zipCode, "")
    If address <> ",  " Then
       current.FormFields("address").result = address
       current.FormFields("address").result = ""
    End If
    Select Case action
        Case actionMODIFY
              ' User wants to open file
              AppActivate "Client Information.doc", False
        Case actionCLOSE
              ' User wants to create file
        Case Else
              MsgBox "Option is not valid."
    End Select

    Set fs = Nothing
    Set wd_app = Nothing
    Set current = Nothing

    Exit Function

    MsgBox Err.Description
    Resume Exit_createFile_ClientInformation
End Function

Thanks for your help.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cindyrodAuthor Commented:
I forgot to mention a very important requirement (mentioned in the title of the question). I know I can do this by calling every function that creates a file, the problem is that I want this to be done in the background. If I call every single function to create a file, it opens every document, loads the data, and closes the document. Is there an easier way to just create the documents using the templates, load the data quickly, and not having to open/display the documents.

I also want to have a form with a rectangle that grows as documents are created (as the ones displayed during setup to show you how much has been done). I was thinking about using a for loop for this ...
You will need to loop through all your functions but there are some tweaks that should help things

set your Word app as global
 Global wd_app As Word.Application
do this at the top of the module

if you call your function from a seperate function then you can set the Word aplication once and leave it open or all the other functions so that you dont open and close it every time, just open and close the files each time.

remeber to rem Set wd_app = Nothing out of the individual functions though

 Word is only showing because you tell it too! :-)  Rem out this line "wd_app.Visible = True" and you won't see it


cindyrodAuthor Commented:
I tried commenting that line ("wd_app.Visible  = True"), and I got an error message (I don't remember what it was). I also tried setting it to False, and I got the same error message.

I just read something about Mail Merge. Would that work in this case and would it be faster? If I just loop through all of my functions, it gets REALLY slow.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I think that the reason why you are getting an error message on commenting out the wd_app.Visible = True line is because the following line is trying to set the current active document ...but you cant do that because its not visible

Either of the following should work:

wd_app.Visible = True
Set current = wd_app.ActiveDocument
wd_app.Visible = false


'wd_app.Visible = True
Set current = wd_app(path & "Client Information.doc")
At a guess it is "Set current = wd_app.ActiveDocument" that needed it visible, you may be able to get around this by setting the value earlier
Set current = wd_app.Documents.Add Template:=theTemplate

I am not sure how you would do this by mailmerge, but then I have not realy used it much to know.

cindyrodAuthor Commented:

I tried that before but it didn't work. I got an error message (I don't remember what the error was, maybe the Add method doesn't return anything or something like that).


I tried your first code: the code still ran very slow and you could see part of the document still showing over the form (I guess the screen was being repainted or something). So there was really no effect on performance and Word still shows up.

I tried your second code and it didn't work. It said something like "invalid property".

For both,

I really appreciate your help. But what I want to do is to create the documents quickly, in the background. I don't want Word to show up. Also, I want to display a bar to let the user know that something is happening (a rectangle that grows as the statements are executed, as during installation of a program).
cindyrodAuthor Commented:
I don't necesarily have to use the function that I posted above. If there is something better and faster, that's what I want.

cindyrodAuthor Commented:
Can I update a Word document without displaying it? If so, how?
I do this with ADO recordsets, and find it pretty much instantaneous....

This uses ADO recordsets, so you will need to define a reference in access vba: Tools>References and then make sure that Microsoft Active X Data Objects 2.X is selected

Here is my full code, which should get you started. Let me know if you need any assistance.

Option Compare Database
Const DOC_PATH As String = "\\Full\Pathto\YourWord\Document\"
Const DOC_NAME As String = "Standard Offer Letter" 'Name of your word document, saved as a Word Template
Option Explicit

Dim appWord As Word.Application
Dim doc As Word.Document
Dim rsModel As ADODB.Recordset
Dim rsContactDetails As ADODB.Recordset
Dim strSQLModel As String
Dim strSQLContactDetails As String
Dim msgA
Dim strDocName As String
Dim blnContactDetailsID As Boolean

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
    Set appWord = New Word.Application
    Err = 0
End If

'Set up the Word variables
    With appWord
        Set doc = .Documents(DOC_NAME)
        If Err = 0 Then
            If MsgBox("Do you want to save the current document " _
                & "before updating the data?", vbYesNo) = vbYes Then
            End If
        doc.Close False
        End If
        On Error GoTo ErrorHandler
        Set doc = .Documents.Open(DOC_PATH & DOC_NAME, , True)
        Set rsModel = New ADODB.Recordset
        strSQLModel = "SELECT tblModelUnits.*, tblModelSite.ProjectName" & _
                    " FROM tblModelUnits " & _
                    " INNER JOIN tblModelSite ON tblModelUnits.ModelID = tblModelSite.ModelID" & _
                    " WHERE tblModelUnits.ModelID =" & Me.txtModelID & ";"
        rsModel.Open strSQLModel, CurrentProject.Connection, _
                adOpenStatic, adLockReadOnly
            With doc
            'Populate word
            .FormFields("FormField1").Result = Nz(rsModel!Field1, 0)
            .FormFields("FormField1").Result = Nz(rsModel!Field1, 0)
           End With
        .Visible = True
    End With

'Tidy up
    Set rsModel = Nothing
    Set rsContactDetails = Nothing
    Set doc = Nothing
    Set appWord = Nothing
    Exit Sub

MsgBox Err & Err.Description
End Sub

By the way, this doesnt automatically save the document, so you will have to build it in, BUT once you have done that, you can probably comment out these lines in my code to prevent word actually displaying:

        .Visible = True
cindyrodAuthor Commented:
Do you mean that instead of using the actual values in the form, I should create a recordset of the values in the form and use those instead?

I'll try that ...
cindyrodAuthor Commented:
I tried it without the recordset to see how it worked, but it was modifying the actual template, not creating a document based on the template.
cindyrodAuthor Commented:
OK, I think it'll be a lot better to start from scratch.

I just want to click a button on a bound form, create a folder for the specific record, create all the necessary files in this folder, and load the data from the form to the files. Everything must be done quickly and in the background, and the only action that the user has to perform is to click the button.

Please HELP, it's urgent!!!!
cindyrodAuthor Commented:
I just decided to export the data to an Excel sheet and mail merge the Word Documents to this sheet. This works perfectly, in the background, and it's really quick. The Word Document Template is merged to this sheet and when the user clicks a button to create the files, the code changes the data source to the specific Excel sheet.

This is some of my code (allFiles is an array of the filenames that I need):

Private Sub cmd_createFiles_Click()

Dim i As Integer

If (copyFolder(Me.jobName.Value)) Then
   Call exportToExcel
   For i = 0 To (NUMBER_OF_FILES - 1)
      Call mergeIt(Me.jobName.Value, allFiles(i))
End If

End Sub

Public Function copyFolder(jobName As String)

Dim fso As Object
Dim i As Integer

'create instance of file system object
Set fso = CreateObject("Scripting.FileSystemObject")

If Dir(DOCS_PATH & jobName, vbDirectory) = "" Then
   fso.createfolder (DOCS_PATH & jobName)
   Call Shell("explorer " & DOCS_PATH & jobName, vbNormalFocus)
   copyFolder = False
End If
'loop through each file in the folder
For i = 0 To (NUMBER_OF_FILES - 1)
    'copy files one at a time
    fso.copyfile TEMPLATE_PATH & allFiles(i) & ".dot", DOCS_PATH & jobName & "\" & allFiles(i) & ".doc"
Call Shell("explorer " & DOCS_PATH & jobName, vbNormalFocus)
copyFolder = True
'clean up
Set fso = Nothing

End Function

Function mergeIt(jobName As String, nameOfFile As String)

Dim fileName As String
Dim objWord As Word.Document

fileName = nameOfFile & ".doc"

Set objWord = GetObject(DOCS_PATH & jobName & "\" & fileName, "Word.Document")

'Set the mail merge data source
objWord.MailMerge.OpenDataSource _
   Name:=DOCS_PATH & jobName & "\Job Data.xls", ConfirmConversions:=True, _
   ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
   PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
   WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
   Connection:="DSN=Excel Files;DBQ=" & DOCS_PATH & jobName & "\Job Data.xls" & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;", _
   sqlStatement:="SELECT * FROM `Temporary_Job_Data`", SQLStatement1:="", _


End Function

Public Function exportToExcel()

Dim currentJob As String
Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("Temporary Job Data")

currentJob = "SELECT * FROM jobs WHERE salesManagerID = " & Me.salesManagerID.Value & _
             " AND jobName = '" & Me.jobName.Value & "'"
qdf.sql = currentJob

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
         "Temporary Job Data", DOCS_PATH & Me.jobName.Value & "/Job Data.xls"

End Function

But thank you everybody for your help!!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cindyrodAuthor Commented:
JonoBB and Bat17,

I have posted a question in
to give you some points for your help. Please follow the link and post a comment.

Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.