Exporting from Access to a Word Template

[Greg W]

I keep track of client contacts using an access table. I would like to be
able to export that contact information to a Word Letter Template, but only
the client name, address, & phone, and only for one particular client at a
time. It's my understanding that Mail Merge will do something close to this
but exports everything. I also need the data to be exported into this word
template file in particular locations.

Outputto seems to output the data in a tabular format.

Any assistance would be greatly appreciated.

 

[John Nurick]

Hi Greg,

There are basically two ways to go. Either use mailmerge - but with a
query that returns only the one record you want - or use Automation.

Simple mailmerge application
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Sending the Current Record to Word with Automation
http://support.microsoft.com/?id=131583 (Word 97)
http://support.microsoft.com/?id=209976 (Word 2000+)


On Wed, 27 Apr 2005 16:22:53 -0700, Greg W <Greg

 

[Alex White MCDBA MCSE]

Hi Greg,

For the simple stuff mail merge is the answer, to start to get clever (I
want to create more than 1 document, different types depending on the data,
and some formatting depending on the data etc.), use automation, within
automation, if your requirement is that only one copy of each field could
ever land on a page then bookmarks in word is the way forward, e.g. create
bookmarks throughout your document called bkFirst_Name then when opening the
document via automation search for all the bookmarks and replace with the
relevant values in the table, if the requirement is you may have more than
one instance of a field appearing on a document then life gets a bit more
complicated, then the use of search and replace on the document is required.
The use of search and replace on any version of word lower than 2003 has a
major bug, search for a string and replace with a string longer than 256
chars and word shoots its self in the head and crashes. If you want some
code I have use for the more advanced stuff your welcome to it, I have
overcome the search and replace bug.

Post back here if you need more help.

 

[Greg W]

yeah I suppose a find and replace could potentially work, however, I think I
am looking for something slightly more advanced, at least I think so.
Basically what I would like to do is this:

I am viewing my client data in a form within access. I see all my relevant
data for this client such as, address, company name, company contact etc. I
would really like, at this point, a button I can press that would open up my
letter template in word, take the current record I am viewing and export, say
for instance, the address, to the address block of my letter. I would also
at the same like access to export the company contact into the appropriate
greeting area of the letter, and so on and so forth. I dont think a find and
replace will really do what i want to do justice, do you? Not sure though.
What would you think? Maybe this explains better exactly what it is that i
want it to do.

Thanks in advance for the help.

Greg

 

[Albert D. Kallal]

My example word merge by default sends the one record.

And, I don't use those HORRIBLE HORRIBLE bookmarks..but in fact use standard
word merge fields that you just insert
(and, this means no hard coding..or changing of any code for different
tables..or different/new documents. It makes NO sense to have to write NEW
code for each new word template.

Give my word merge example try...

 

[Alex White MCDBA MCSE]

Hi Greg,

in my view search and replace is the most flexable system, a couple of
things to consider, addresses made up of things like,

address1
address2
town
county
country
postcode/zip

what do you do when there is no address2, horible blank lines in the
address, the procedure I use to deal with this is before merging/search and
replace I get all my address fields and pump them into string vars, omiting
blank lines so that the address is tidy.


sqlCandidate = "Select * from TblCandidate Where Candidate_ID=" &
intCandidate_ID & " FOR BROWSE"
With adoCandidate
If .State = adStateOpen Then
.Close
End If
.Open sqlCandidate, CurrentProject.Connection, adOpenStatic,
adLockReadOnly
intPos = 0
If Not IsNull(.Fields("Address").Value) Then
strMySplit = Split(.Fields("Address").Value, vbCrLf)
If UBound(strMySplit) > 0 Then
If Not IsNull(strMySplit(0)) And Len(strMySplit(0)) > 0
Then
strAdd1 = strMySplit(0)
intPos = intPos + 1
End If
End If
If UBound(strMySplit) > 1 Then
If Not IsNull(strMySplit(1)) And Len(strMySplit(1)) > 0
Then
If intPos = 1 Then
strAdd2 = strMySplit(1)
intPos = intPos + 1
End If
If intPos = 0 Then
strAdd1 = strMySplit(1)
intPos = intPos + 1
End If
End If
End If
If UBound(strMySplit) > 2 Then
If Not IsNull(strMySplit(2)) And Len(strMySplit(2)) > 0
Then
If intPos = 2 Then
strAdd3 = strMySplit(2)
End If
If intPos = 1 Then
strAdd2 = strMySplit(2)
End If
If intPos = 0 Then
strAdd1 = strMySplit(2)
End If
intPos = intPos + 1
End If
End If
End If
If Not IsNull(.Fields("Town").Value) Then
If intPos = 3 Then
strAdd4 = .Fields("Town").Value
End If
If intPos = 2 Then
strAdd3 = .Fields("Town").Value
End If
If intPos = 1 Then
strAdd2 = .Fields("Town").Value
End If
If intPos = 0 Then
strAdd1 = .Fields("Town").Value
End If
intPos = intPos + 1
End If
If Not IsNull(.Fields("County").Value) Then
If intPos = 4 Then
strAdd5 = .Fields("County").Value
End If
If intPos = 3 Then
strAdd4 = .Fields("Country").Value
End If
If intPos = 2 Then
strAdd3 = .Fields("County").Value
End If
If intPos = 1 Then
strAdd2 = .Fields("County").Value
End If
If intPos = 0 Then
strAdd1 = .Fields("County").Value
End If
intPos = intPos + 1
End If
If Not IsNull(.Fields("Postcode").Value) Then
If intPos = 5 Then
strAdd6 = .Fields("Postcode").Value
End If
If intPos = 4 Then
strAdd5 = .Fields("Postcode").Value
End If
If intPos = 3 Then
strAdd4 = .Fields("Postcode").Value
End If
If intPos = 2 Then
strAdd3 = .Fields("Postcode").Value
End If
If intPos = 1 Then
strAdd2 = .Fields("Postcode").Value
End If
If intPos = 0 Then
strAdd1 = .Fields("Postcode").Value
End If
intPos = intPos + 1
End If
End With


Hope it helps,

 

[Albert D. Kallal]

what do you do when there is no address2, horible blank lines in the

address, the procedure I use to deal with this is before merging/search
and replace I get all my address fields and pump them into string vars,
omiting blank lines so that the address is tidy.

You should not have to write all that code to solve this problem? Ms-word
will automatically remove blank lines by default! You don't need any code if
you use merge fields. If you place those fields in word

address1
address2
town
county
country
postcode/zip

Any blank line will move up in word if you use merge fields.

in my view search and replace is the most flexable system, a couple of
things to consider, addresses made up of things like,

No, I can't agree with the above. In fact, some companies when they find out
that a programmer, and new code needs to be written for each new template
document will consider that the developer was dishonest. Can you imagine
that for each new document you write that you have to go back to the
developer and write some code? This kind of approach does not look very good
in the customers eyes at all.

Bookmarks, and writing code has it place, and when solutions requite
bookmarks and writing custom code for each document then so be it. However,
using the built in merge fields and providing a general solution for the
customer is going to be the preferred approach.

With my merge example, the customer does NOT have to write any code for each
new document. I can't believe I am actually sitting here discussing about
solutions that requite code vs solutions that requites no NEW code for each
new document). You can use and insert merge fields into a document, and if
any of those fields are empty, then a blank line is not inserted.

Further, while developing the document, you can toggle between showing the
data, and the merge fields.

I don't feel great trying shoot down your point about using bookmarks, and
as I mentioned there are times when using book marks makes sense..but most
of the time they don't, and solutions that use bookmarks usually result in
shortchanging the users.

 

[Alex White MCDBA MCSE]

Hi Albert,

I agree on the bookmark front, they are not a perfect solution but for
simple stuff (e.g. no duplication of fields on the word document), they
suffice, I wrote a system on the beta version of access 2000 in 1999, and
that system has not been upgraded (after office 2000 was released) and uses
a system to scan (search and replace) for new merge fields within the tables
(in the SQL db), and that company has not come back to me once to re-write
the system and yes it is sill in use, to the point where it is producing
5000+ word documents a month and the client is very happy with it. What I
would say is this, that system took about 1 man month to write and was hard
work on my part but it does work without error and without a programmer
being required to get invovled in it at all, the only skills required on the
part of the client is MS Word skills like typing. The other thing that I
found was that conditional formating within the Word/Merge system left a lot
to be desired, e.g. my client needed at a database level to colour/format
text on the word documents but driven by database rules not word macros.

It is always horses for courses, no one solution fits all, hey good debate.

 

[Albert D. Kallal]

I have to say that bookmarks do have some advantages. And, to be fair, there
is a GOOD many examples posted on the net.

Also, there is a GOOD MANY problems if you allow word to "attach" to mdb
file when you use merge fields. The bug of a 2nd copy of ms-access being
launched, and also a bunch of issues when the mdb file is secured comes up.
Not to mention even more problems on machines running multiple versions of
ms-access.

However, the above problems can be mitigated. For example, when I run my
word merge example, I do NOT allow ms-word to attach to the mdb file, but in
fact create a temp merge file between the two. This eliminates all of the
security issues, and also a host of bugs/problems. And, this approach also
works if you have a linked table to sql server, but once again, word NEVER
links to the mdb file.

And, I run my solution with a97, and 3 defender versions of word/office..and
it NEVER have broken (late binding code here).

In fact, what my approach and your book marks suggestion have in comments is
that they both DO NOT allow word to open the mdb file and grab data. It is
this reason that result in increased reliability here..

If you take a quick look at these screen shots, you can see a screen shot
that shows a drop down list of merge fields.

http://www.members.shaw.ca/AlbertKallal/wordmerge/index.html

Users who create a template document NEVER have to remember field names, or
even type in the field names since they appear from a drop down list. (using
bookmarks means that the users actually have to KNOW, and REMEMBER the field
names. And, further, they have to type EXACTLY the correct field name or it
don't work). This is just one of many advantages of using merge fields. The
other big advantages that any person who has taken office training likely
has seen the merge fields and dialog boxes from their "training" courses.
And, finally, the amount of code that needs to be written is FAR less then
code that has to find/replace bookmarks.

Having said the above, there is a lot nice code examples that use
bookmarks.. And, the bookmark solutions tend to be VERY reliable, since they
avoid a file open/merge process.

 

[Alex White MCDBA MCSE]

Hi Albert,

Had a good look at your code and yes nice code, very simple I never thought
of using the word merge functionality, for simple or straight forward
merging looks like it does the job. The requirement that I had to code was a
multi level, merge solution being able to merge from any field in any table
in the database and it works on the relationship model of the database e.g.

proposals
linked to contacts etc,

creating a word document that has proposal and contact information on it, it
opens the proposals table and can work out the linked contact information as
well, my solution takes a good couple of hours to bind into any new project
as it uses database records to build the documents and merge codes. My
system uses search and replace functions with all the problems that has got,
but it works very reliably.

For my standard projects in future I will use your code because 5 minutes of
work and the job is done, for the bigger stuff I will continue to use my
code given the flexibility of conditional formatting/colouring/one to many
control etc.

Another thing I do is when developing word/excel solutions whilst in
development I early bind everything to expose the object model and at
program release convert my code to late binding (best of both worlds).

At the end of the day the job is to provide solutions to clients that we are
proud of and solves the business problem(s).

 

[Greg W]

Hi both Albert and Alex,

Just wanted to write to say thank you so very much for your help. I ended
up doing the word merge approach. It works great. We shall see how everyone
else likes it also, which i am sure they will. Such a reward seeing and
hearing the ewws and uhhs after giving the database an upgrade.

Again i thank you both greatly for your time to post answers to my questions.

Thanks,
greg