Opening MS Word from Access by VBA Script

[chris.kasperski]

I have a problem. I thought it's easy to start some specified Word
document with VBA. And it really is, but the problem is to start
document which uses data from external database and fill some fields
with them. If I start the document manually (by double clicking on it),
Word asks me if I really want to use datas from external database. If I
open this document by VBA script, I'm not asked about anything and
the fields which should contains the data are empty.
I guess it's problem of permittion rights. But I' not sure. Did
anyone has the same problem?? Can someone help me??

 

[Albert D. Kallal]

yes, you can get around that prompt by simply trapping the error, and them
simply setting the datasource again in code.

I have a pre-built word merge solution that handles all of these little
problems and more.

(for example, my word merge works even if your database is secured, or even
if your database has a password on it).

Try my word merge example, it also allows you to word merge enable ANY FORM
in your application with ONE LIEN of code.
Further, this one line of code will actually merge the CURRENT record you
are looking at.....

You can find it here:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

 

[chris.kasperski]

Thank You for your help!!!
Your solution doesn’t work on my computers at all (new documents are
always empty), but I stole some VBA code from You and now it starts to
work. But I still have a lot of troubles. The strangest thing is that
all of it was working until some day. I’ a new worker in a company
and someone before me did the system, so I don’t understand
everything. Why if I open document manually it takes data, and if VBA
script do it doesn’t??
Now with Your code it looks like this:

Set WordDoc = wordApp.Documents.Open("C:\Program Files\TSA\" & strVers)

'wordApp.Visible = True

WordDoc.MailMerge.OpenDataSource _
Name:="C:\Program Files\TSA\Letter Queries.mdb", _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=True, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=True, Format:=0, _
Connection:="", SQLStatement:="SELECT * FROM
[tblPrintProjectLetter]", SQLStatement1:=""

With WordDoc.MailMerge
.Destination = 0 ' 0 = new doc
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
' .LastRecord = 1
End With
.Execute Pause:=False
End With

But this opens a file which it should and leave it empty and open
additional document which is filled correctly. Is it possible to fill
current document with data instead of making new one? And at least is
it possible to close the unfilled (if the word like this exists ïŠ )
one ??

Please help me if You find a little bit of time.

 

[chris.kasperski]

And one more thing - mayby it will help You.
With help of Your code I get two open documents - one (mine) empty, and
Your filled correctly. All the buttons in Mail Marge bar are active (on
Your document not). But when I click "Viev Merged Data" button
everything is OK. All the datas comes. Sorry - I realized it now. How
to make Word to View Merged datas automaticly??

Thanks a lot

Chris

 

[chris.kasperski]

Now I got it. I need only this:

Set WordDoc = wordApp.Documents.Open("C:\Program Files\TSA\" & strVers)


'wordApp.Visible = True

WordDoc.MailMerge.OpenDataSource _
Name:="C:\Program Files\TSA\Letter Queries.mdb", _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=True, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=True, Format:=0, _
Connection:="", SQLStatement:="SELECT * FROM
[tblPrintProjectLetter]", SQLStatement1:=""

This start my document ant execute SQL querry. Now I only have to click
"View Merged Data" button and everythink is OK. But how to avoid it.
how to avoid clicking the button??

 

[Albert D. Kallal]

Thank You for your help!!!

Your solution doesn't work on my computers at all (new documents are

always empty)

First, we need to get the example download working. If my example does NOT
work, then we are just on a big huge wild goose chase, and are wasting each
others time.

We MUST ensure that the sample download works, THEN, AND ONLY THEN CAN we
being the problem solving process.

So, first things to check:

download the example, and try a test merge to a document. does it work?

(remember, the merge example will NOT work if you don't place, or have AT
LEAST ONE merge field in the document.

So, first, try the sample download. Remember to place at least ONE field in
the word merge document. WE MUST get this working.

Try the above...does the sample download as is, un-modifed work?

 

[chris.kasperski]

OK. Your sample works perfect!!! But it works like this:
it opens the template (without filling it), then open next document
(fill it) and than close the previous (unfilled) one. It's OK. But I
still wonder how to make it in my way. It open and it executes the
querry. Everythink is OK - I just have to click his one button. It must
exist some VBA function which executes the same command which "View
Merged Data" does.
Anyway thanks a lot!! If I won't find a command to execute "View Merged
Data" I will use Your way.
One again Thanks. If tou have some ideas - I will be glad

Chris

 

[Albert D. Kallal]

OK. Your sample works perfect!!! But it works like this:
it opens the template (without filling it)

I don't understand the above??? The template should fill out.
You are given several options. You are given the options to "modify the
template" After you save this, then you are back at the merge:

You also have the option to "ok, merge to word"

If you use the "merge" to word, then the word template is MOST certainly
filled out. So, it is confusing as to say that the template is opened
without filing it out? Somthing is wrong here?? You need to for the first
time modify (or add) a template. This will give you a chance to insert some
field names into the document. Do NOT skip this step (it only needs be done
once).

then open next document
(fill it) and than close the previous (unfilled) one.

Hum, I don't understand the above at all? "close the previous" one??????
Do you want to merge more the current record? If you need to merge "many" to
that one template, then check out the download, there is a button on the
contract form called "merge all records".

So, use my sample to build a template, and then close/save that template.
Note how there is a "merge all" button..that will send "many" records to the
one template.

It's OK. But I
still wonder how to make it in my way. It open and it executes the
querry.

yes, a query, or sql can be send to the word document. To send any sql to
the word merge? Sure, just do the following:

You can use my sample library with a query, or sql

dim strSql as string

strSql = "select * from tableCustomers where city = 'Edmonton' "

MergeAllWord strSql

The above would send that sql to my merge system. (I think that is what you
are looking for).

Once you get familiar with the above, and using queries with my merge
system, then you can by-pass the "prompts". How to do this is explained
here:

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

So, if you are looking to send particular sql to a merge document, my
examples well handle this. And, if you eventually don't want to prompt the
user for what word document, again my routines can do this without those
sample forms I have.