Controlling Word from Access 3

[sda7bobp]

I have never used the Shell function before, so I am not certain just how to get done what I need. I need to open Word from Access, then open a form letter template that gets its data from the Access database from which I am opening Word. Then merge the data and print the letters. I can open Word with the Shell function, but then when I attempt to open the form letter template, the SendKeys sends the information back to Access and not to Word. What do I need to do differently?
Thanks,
Bob

 

[TomHW]

You should look into automation as opposed to Shell and SendKeys. It will be much easier to manipulate and things will behave a little more consistently. Through automation you can create a Word object and manipulate the object in Access. This way you work with the actual object model instead of trying to emulate a user interacting with the program using SendKeys.

Hope this helps,
Tom

 

[PaulBricker]

The shell function won't get you there I don't think. You need to work inside Word directly.

Public Function MergeIt()
Dim odoc As Word.Document
Set odoc = GetObject("PathtoyourFormLetter", "Word Document")
odoc.MailMerge.OpenDataSource _
Name:="PathTo.mdbFile", _
linktosource: = True, _
connection:="QUERY querynamehere", _
SQLStatement:="Select * From SomeTable"
odoc.MailMerge.Execute

End Function

This will get you closer. Try it out and post back with problems.

Paul

 

[sda7bobp]

Thanks, Paul and Tom. I have just gotten back to working on this and will try your suggestions.
Bob

 

[sda7bobp]

Paul,
I get an error with the Dim statement: "User defined type not defined".

 Public Function MergeIt()
[COLOR=red] Dim odoc As Word.Document [/color]
Set odoc = GetObject("PathtoyourFormLetter", "Word Document")
odoc.MailMerge.OpenDataSource _
Name:="PathTo.mdbFile", _
linktosource: = True, _
connection:="QUERY querynamehere", _
SQLStatement:="Select * From SomeTable"
odoc.MailMerge.Execute

End Function

 

[sda7bobp]

Oh, I'm running Access 97
Bob

 

[KenReay]

Hi

You need to set a reference to the Word object library, in code module, design view, see tools \ references

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sda7bobp]

Ken,
I find available references, but from there I am lost. Any help would be appreciated.
Bob

 

[PaulBricker]

Bob, if you open your module and go to Tool...references on the menu bar, you will see the available libraries. Then scroll down till you see the library
Microsoft Word 9.0 Object Library (Access 97 will probably have a version like 8.0 or something)
Put a check in the box next to that library and close everything out. That should do what you need.


Paul

 

[sda7bobp]

I am getting an error "ActiveX component can't create object"

Public Function MergeIt()

Dim odoc As Word.Document
[COLOR=red]Set odoc = GetObject("C:\Program Files\Microsoft Office\Templates\LymePositive.dot", "Word Document")[/color]
odoc.MailMerge.OpenDataSource _
Name:="C:\My Documents\InfectiousDiseases\LymeDiseaseDatabase.mdb", _
linktosource:=True, _
Connection:="QUERY qryLetter", _
SQLStatement:="Select * From tblData"
odoc.MailMerge.Execute



End Function

I'm also concerned about the "Connection: = "QUERY qryLetter" line. Is that correct?

One more question. Do I need the SQL statement if there is a query statement? If so, should it be the same as would be for the query?

Unfortunately, I have no help information for this sort of code. Thanks for your help.
Bob

 

[PHV]

You may consider something like this:
Set objWord = CreateObject("Word.Application")
Set objWord.Visible = True
Set odoc = objWord.Documents.Add("C:\Program Files\Microsoft Office\Templates\LymePositive.dot")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PaulBricker]

I think you may need the ADO library as well as the Word library. In your library References, there is one for Microsoft ActiveX Data Objects 2.x Library. You probably need that one checked as well.

As for the rest of your questions, that code is what I have used to merge documents with for a long time. I can tell you that it is correct, but whether or not you can get rid of any of it, I'm not sure. The lines that have the "line continuation character" ( _) at the end of them are all one line of code. They are all arguments of the odoc.MailMerge.OpenDataSource object.
Also, my code shows the SQLStatement line be
SQLStatement:"Select * From qryLetter" the query listed in the QUERY: argument above it.

Try setting your references, and see how it works. If it bombs on anything, let us know. As I say, I've used this bit of code for years to do Mail Merge from on of our Summer Programs databases but it may need a little tweaking for your purposes.

Paul

 

[sda7bobp]

Paul,
I really thank you for the time you have put into assisting me in this bit of code.

In the Reference library I find 3 Microsoft ActiveX Data Objects 2.x Libraries. The program will only allow 1 of those to be checked at a time or it will yield a naming conflict error. They are 2.1, 2.5 and 2.6. I have tried each of these sequentially and still get the same message: "ActiveX component can't create object.
Bob

 

[PHV]

Have you tried my suggestion to instantiate the Word.Document object ?

 

[PaulBricker]

If one ActiveX is selected, you can't select anymore. You can try PHV's suggestion. I don't have mine running that way, but I'm using 2000, not 97 so it's possible that would make a difference. Also, you could list the other libraries you have checked and I can compare them to what I have selected in my db.

Paul

 

[PaulBricker]

Something else. Is the document named LymePositive.dot or is that a typo. Is word trying to open a .dot when it should be .doc.

Paul

 

[sda7bobp]

PH and Paul,
The code that is presently working and doing very well is:

Public Function MergeIt1()
Dim odoc As Word.Document, objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set odoc = objWord.Documents.Add("C:\Program Files\Microsoft Office\Templates\Lyme Positive.dot")
odoc.MailMerge.OpenDataSource _
Name:="C:\My Documents\Infectious Diseases\LymeDiseaseDatabase.mdb", _
linktosource:=True, _
Connection:="QUERY qryLetter", _
SQLStatement:="Select * From qryLetter"
odoc.MailMerge.Execute
odoc.PrintOut

End Function

It is a combination of both of your suggestions, and seems to work just fine. I will need to keep a copy of this for future use in other databases. Thanks again.
Bob

 

[PaulBricker]

Well done Bob.


Paul