Hi Norbert,
for automation with Word, I have an example here:
this example uses bookmarks but there are other ways ...
and choose:
Warm Regards,
Crystal
~ have an awesome day ~
On Saturday, December 20, 2014 6:02 PM, "jpjones23@centurylink.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Just PSHAW! I hit send prematurely. My apologies.
Please, once you've looked at this wonderfulness, don't hesitate to ask questions. (I know that ADO is currently preferred over DAO but after 30+ years of cutting code, I'll use whatever approach is available. DAO still works and the database I just built has a limited lifespan.)
Jeff
Norbert,
Here's the basic structure that I'm using.
1. All MS Word templates are on a shared drive to enable all users to access them.
2. The full, dynamic, path of the location of the templates is in a single record table all by itself. This allows for the template location to be moved at any time and only one record needs be updated. Whenever a template is needed or its document is saved, the dynamic path is concatenated to the front of the file name to get or save the file.
3. The names of the MS Word templates live in another table and are selected from a drop down list by the user when needed. The template table has a 1 to many relationship with a child table that lists the template fields to be replaced and the table and field within the table used to replace the template field. Taking this approach allowed me to cut code that always knows how to personalize every letter and to do so in a manner such that once built, the user has total control over what should be personalized in each letter and what data to use to do so.
4. Whenever a letter is sent, there needs to be an audit trail so the physical file name of the letter that's been created is stored in an audit trail table that has a many relationship to each letter's recipient. This allows for multiple letters to be sent to the same individual.
5. This letter's file name is displayed in a text box with the ability for the user to double click on it when desired to open it via a shell function. Just in case, this works with any type of file as long as it's file extension has an association with an application on the PC that will open it. In my case, I can store Word documents as well as *.pdf, *.jpg, and *.msg files if the users wish.
6. With my database structure where I have a primary table with 5 related tables with a many relationship to the primary I had to allow for letters to be sent to groups of people from groups of people to to individuals assocyaed with either group. The subject of the template controlled this so the template table has a few extra fields that I use to determine if, when building a letter, it goes to everyone at once or a separate letter goes to each person individually.
I mentioned some "gotcha's" so here they are.
1. Every field in a MS Word template that I want to replace with data from the database is bookended with percent signs. Therefore the actual name of a person to whom the letter is being sent might be "Dear %recipient%," (no quotes) in the Word document. You'll see how the code does the replace a bit further down.
2. At first I left the Word documents open so the user could make manual changes. I discovered that if I did that, and even though I could make the changes and successfully save the document, I couldn't open it later. MS Word said that it couldn't open the file. Therefore the Access code opens the Word template, does the personalization, saves the document, quits Word, sets the object to nothing, and then creates the audit record with the save file name in it. Then the user could immediately open the document with the double click because I did a refresh of the form to get the file name displayed.
The Template field table has the following fields.
TemplateID (Template Table key)
TemplateField - %recipient%,
DatabaseTable – tblRecipient
DatabaseField - RecipientName
Here's the code that creates the Word object, opens the template, replaces fields, save the document and closes Word. Based on the template being chosen, it was cleaner and easier to create tailored queries and set each to a record set that gets passed to this function. This allowed me to use the template specific table that is also passed to control the replacements prior to the document save.
Function NowCreateLetter(sGnuDocument, strAttchFldr, strTblFlds, Optional rsInsured As DAO.Recordset, Optional rsBeneficiary As DAO.Recordset, Optional rsRecipient As DAO.Recordset)
Dim i As Long
Dim arrReplVlus As Variant
Dim strTemp As String
Dim objWord As Word.Application
On Error GoTo HandleError
'Launch word and load the template
Set objWord = CreateObject("Word.Application")
objWord.Documents.Add strAttchFldr
objWord.Visible = True
'Add information to the template %CurrentDate%
With objWord.ActiveDocument
.Range.Text = Replace(.Range.Text, "%CurrentDate%", _
Date, , , vbTextCompare)
For i = LBound(strTblFlds) To UBound(strTblFlds)
If Not Len(strTblFlds(i)) = 0 Then
arrReplVlus = Split(strTblFlds(i), "~")
If arrReplVlus(0) = "tblInsured" Then
strTemp = rsInsured.Fields(arrReplVlus(1))
ElseIf arrReplVlus(0) = "tblBeneficiary" Then
strTemp = " " & rsBeneficiary.Fields(arrReplVlus(1))
ElseIf arrReplVlus(0) = "tblRecipient" Then
strTemp = " " & rsRecipient.Fields(arrReplVlus(1))
End If
If Not InStr(.Range.Text, "Company ID") > 0 Then
If Not rsRecipient.Fields("Company") Then
.Range.Text = Replace(.Range.Text, "As the treasurer of the %RecipientName% Company, you", _
"You", , , vbTextCompare)
.Range.Text = Replace(.Range.Text, " Company", _
"", , , vbTextCompare)
End If
End If
End If
.Range.Text = Replace(.Range.Text, arrReplVlus(2), _
Trim(strTemp), , , vbTextCompare)
End If
Next i
.SaveAs sGnuDocument
NowCreateLetter = sGnuDocument
End With
HandleError_Exit:
'Exit Word
objWord.Quit
Exit Function
Please, once you've looked at this wonderfulness, don't hesitate to ask questions. (I know that ADO is currently preferred over DAO but after 30+ years of cutting code, I'll use whatever approach is available. DAO still works and the database I just built has a limited lifespan.)
Jeff
From: "drnorbert@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, December 20, 2014 8:23:14 AM
Subject: Re: [MS_AccessPros] Convert reports (letters with if conditions) from Access 2013 to Word 2013
--
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, December 20, 2014 8:23:14 AM
Subject: Re: [MS_AccessPros] Convert reports (letters with if conditions) from Access 2013 to Word 2013
Hi Jeffrey,
Thanks for your explanation.
Yes is exactly what I need.
Can I get in touch with you?
Much regards
Norbert
--
---
Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
http://ExcelAndAccess.Com
919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net
Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
http://ExcelAndAccess.Com
919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net
__._,_.___
Posted by: Crystal <strive4peace2008@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar