Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Create Word documents from templates in background

Posted on 2004-11-03
16
Medium Priority
?
990 Views
Last Modified: 2008-08-19
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 Information.dot"

    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"
    Else
       If Not (IsFileOpen(path & "Client Information.doc")) Then
          wd_app.Documents.Open filename:=path & "Client Information.doc", ReadOnly:=False
       Else
          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")
    Else
       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(Me.city, "") & ", " & Nz(UCase(Me.state), "") & " " & Nz(Me.zipCode, "")
    If address <> ",  " Then
       current.FormFields("address").result = address
    Else
       current.FormFields("address").result = ""
    End If
   
    current.Save
   
    Select Case action
        Case actionMODIFY
              ' User wants to open file
              AppActivate "Client Information.doc", False
        Case actionCLOSE
              ' User wants to create file
              current.Close
              wd_app.Quit
        Case Else
              MsgBox "Option is not valid."
    End Select

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

Exit_createFile_ClientInformation:
    Exit Function

Err_createFile_ClientInformation:
    MsgBox Err.Description
    Resume Exit_createFile_ClientInformation
   
End Function

Thanks for your help.
0
Comment
Question by:cindyrod
  • 10
  • 3
  • 2
15 Comments
 
LVL 1

Author Comment

by:cindyrod
ID: 12483391
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 ...
0
 
LVL 9

Expert Comment

by:Bat17
ID: 12483704
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

peter


0
 
LVL 1

Author Comment

by:cindyrod
ID: 12483742
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.

Thanks.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 8

Expert Comment

by:JonoBB
ID: 12484043
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


OR

'wd_app.Visible = True
Set current = wd_app(path & "Client Information.doc")
0
 
LVL 9

Expert Comment

by:Bat17
ID: 12484069
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.

peter
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12484663
Peter,

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).

JonoBB,

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).
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12484674
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.

THANK YOU!
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12484731
Can I update a Word document without displaying it? If so, how?
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12484926
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
                    .Dialogs(wdDialogFileSaveAs).Show
            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)
             'Etc
           End With
   
       
        .Visible = True
        .Activate
       
    End With

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

ErrorHandler:
MsgBox Err & Err.Description
End Sub

0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12484944
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
        .Activate
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12486071
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 ...
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12486173
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.
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12497940
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!!!!
0
 
LVL 1

Accepted Solution

by:
cindyrod earned 0 total points
ID: 12566329
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))
   Next
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)
Else
   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"
Next
     
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:="", _
   SubType:=wdMergeSubTypeOther

objWord.Save
objWord.Application.Quit

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!!
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12587833
JonoBB and Bat17,

I have posted a question in http://www.experts-exchange.com/Databases/MS_Access/Q_21207605.html
to give you some points for your help. Please follow the link and post a comment.

Thank you!
Cindy
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question