Henry Simpson
hsimpson88 at hotmail.com
Wed Mar 12 11:40:00 CST 2003
I have done a few simple merges but generally rely on bookmarks simply
because I find them simpler and more intuitive when using VBA. Using
bookmarks lets you move all the decision logic into a code module rather
than a bunch of inline If statements. Note also, that bookmarks in
conjunction with VBA permit you to use code to insert not only text but form
fields or merge codes and do complex conditional formatting not possible
with merge codes alone.
The main reason I use automation is that VBA permits me to create folders
and save files in folders with specific names and verify the existence of
templates and existing documents. Something an unsophisticated user may not
be able to do is customize a query to a specific set of records or quickly
find a list of templates appropriate to a certain kind of record. My
document generation form opens from one of several main forms as a pop up.
When opened from a member form, a document template selection combo is
filled with a list of all documents in a folder beneath the path to the
database containing the table tblMembers in a subfolder named
\MemberTemplates\. Another combo permits me to pop a membership filtering
parameter form that sets the query of people to whom the chosen document
should be sent. If this isn't selected, a document only for the current
member record is created. The doc generation form also has a series of
checkboxes that 1) Print the Document, 2) Save the Document in the Member
Record Folder, 3) Save the document in the Output Folder 4) Create if
Document Doesn't exist 5) Overwrite if exists 6)Suffix with new number if
exists.
A typical example is, we have 400 members who need a renewal notice. The
user can select Renewal Notice as the Document Type. The user may
excessively filter the target list. When they select a broader filter, they
may choose to generate all the docs again or simply create those that don't
already exist. A bonus is that each document may be saved in each member's
folder but for batch printing, all the files can be generated into a single
folder and emailed from a single folder yet a copy can remain on each member
file.
I have failed to have much luck with merge codes in documents with form
fields and with codes in headers and footers. Also, should the text span 1
or more than one page in various circumstances, doing it with VBA lets you
select a different subsequent page header and adapt automatically to
different document lengths. VBA also permits to you use password protected
documents and lock and unlock programmatically.
I can't point to any particular books as I have many and they all have their
strengths. A good place to start is
www.helenfeddema.com/ online and of course, the examples at this
www.databaseadvisors.com. I'll see if I can whittle out an example of some
stuff and move this offline for the time being.
Hen
>From: "Klos, Susan" <Susan.Klos at fldoe.org>
>Reply-To: accessd at databaseadvisors.com
>To: "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com>
>Subject: RE: [AccessD] Word Automation
>Date: Wed, 12 Mar 2003 08:12:23 -0500
>
>Henry, I would like to learn more about how you do this. I have a new
>client who is doing a merge currently from Excel. His Master word document
>is set up with a bunch of field codes. He does a mail merge from excel and
>has a bunch of if statements in the master document that put different
>responses in depending on the information in the excel field. He know he
>needs to store the data in Access and we have had a session (he wants to
>learn Access at the same time we are doing this) where he learned how to
>create tables, set primary keys and relationships. There are 4 or 5 tables
>at present, but there is no table set up that would hold the responses yet.
>He is comfortable with the way he has set up his word document and I am not
>sure how to show him that storing everything in Access would be better than
>doing it the way he is currently. My gut feeling is that it would be. I
>will try to give you an example of what he has in his word document.
>
><p> blah blah blah {IF{MERGEFIELED whatever}="a" "Some text goes here"}blah
>blah blah. He might have several of these if statements depending on what
>is in the field. ie a=some text b=some other text c=some other text
>altogether The field from the database might also trigger a document that
>is on the network that gets merged into the master doc at some point.
>
>The master doc is an RFP / Contract. The RFP gets filled in first then the
>contract gets filled in.
>
>I guess what I am asking is 1)should the text be held in the database
>rather
>than the codes that trigger the if statements in the word doc. 2)would just
>inserting merge fields into the doc work or should bookmarks be used? Why
>would bookmarks be better if they are?
>
>Maybe you could recommend a book that would be good background on this sort
>of thing.
>
>-----Original Message-----
>From: Henry Simpson [mailto:hsimpson88 at hotmail.com]
>Sent: Tuesday, March 11, 2003 4:47 PM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] Word Automation
>
>
>If you invest a significant amount of work in Word, you will find that an
>Automation session of Word run by Access can iterate the bookmarks
>collection of the document object. I have given the users a list of
>bookmark names that they can use in any document. If they save that
>document as a template in a particular folder, that template file will
>appear in a callback filled combo of documents that may be created from a
>record or query. Double click of any template file in the combo will
>create
>
>a new Word doc based on the template for the record or each record in a
>query should that source be selected.
>
>For versatility with things like date datatypes, users have been trained to
>use a bookmark named DocDate for 'March 11, 2003' or DocDate_dd_mmm for '11
>Mar'. Since bookmarks can not contain punctuation, should punctuation be
>required, the Date must be broken down into components with the punctuation
>between bookmarks. If the suffix of any bookmark containing a parsable
>format string ends in 'ord' and begins with 'DocDate', then the appropriate
>th, rd or nd suffix is appended in Word. Should the document require the
>current month or the month of a project start date or an invoice date or
>whatever, in several places, the bookmark name cannot be repeated. In that
>case, the users Prefix the bookmark with DocDate1, DocDate2 and so on.
>
>For things like this, all that happens is that the application iterates and
>parses each bookmark name and selects the appropriate field and format to
>write to Word. Should the field parsed data type happen to be currency, a
>formatCurrency function is called that formats the data to a currency
>string.
>
>Users may now point any query at any document in their custom templates
>folder and Access will generate a series of documents. What's more, the
>users need only be able to set parameters from a multi field filter
>interface and not only is the query customized accordingly, related records
>are pulled even though they may not be required by the document. The price
>of flexibility is a bit of overhead in the queries as a Project record may
>have related invoice data when all that is needed is PO information.
>
>Not one of the users I deal with knows how to generate a merge, never mind
>a
>
>query, but they can select a record from a combo and punch a button. And
>now, they can even use the insert menu from Word and insert one of about
>200
>
>allowed bookmark names. They have even figured out how to save the file in
>their custom template folder. The biggest training hurdle was to get them
>to add a suffix to a field name of the bookmark should it be required in
>more than one place in the document.
>
>Although considerable effort and thought went into the original system, it
>is now virtually maintenance free and I am rarely called in for minor
>changes to existing document templates or even entirely new documents.
>
>
>Hen
>
> >From: Brett Barabash <BBarabash at tappeconstruction.com>
> >Reply-To: accessd at databaseadvisors.com
> >To: "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com>
> >Subject: RE: [AccessD] Word Automation
> >Date: Tue, 11 Mar 2003 14:41:35 -0600
> >
> >I've seen this discussed before on this list, and wondered why people are
> >rolling their own mail merge routines? Personally, I find bookmarks
> >cumbersome to work with and mail merge has always worked well for me with
> >minimal effort.
> >
> >
> >-----Original Message-----
> >From: John Bartow [mailto:jbartow at earthlink.net]
> >Sent: Tuesday, March 11, 2003 10:38 AM
> >To: AccessD
> >Subject: [AccessD] Word Automation
> >
> >
> >Bryan (or any other word gurus out there),
> >Can you think of a way to elegantly replace my (word automation) mail
> >merges
> >with your bookmark style automation?
> >
> >Basically use bokmarks and some looping scheme to dump addresses it a
> >prepared letter/envelope, creating one document with all of the merged
> >letters/envelopes.
> >
> >JB
> >_______________________________________________
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>
>_________________________________________________________________
>MSN 8 with e-mail virus protection service: 2 months FREE*
>http://join.msn.com/?page=features/virus
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail