Open Word Doc from Access (VBA/link?)

[ITC]

I have created a mail merge in Word based on a parameter query in Access. (This is designed to create a hire letter that can be pulled up one employee at a time and to include all relavant info to that employee in the letter.) I wish to make the opening of this mail merge extreamly user friendly.
I have tried to link to the document but upon using the link, all applications close.
I have tried to create a macro (hopefully to be placed on a form as a drop down list because I also have other letters I wish to mail merge from Access) but have been unsuccessful at opening any more than just the application.
I have been referred to VBA for the answer, but I am unfamilar with the syntax, or even how to get started. Can this be accomplished in a module? And how might I go about this task?

Any and all help will be appreciated.

Viska
International Telecommunications Consultants.

 

[Scoty]

Viska,
I used to open forms on a network drive just by adding a space.. As in:

Private Sub OpenLetter_Click()
Dim stAppName as String
stAppName = "C:\Program Files\Office97\Office\Winword.exe g:\Network\Subdrive\MyDrive\MyDoc.Doc"
Call Shell(stAppName, 1)
End Sub

Notice the space between my app Winword.exe and the location of my document. I Personally don't use merge only because I can't keep track of the letters that are generated. I just Build a report that looks like the letter and the print my report off on letter head. Let me know if this helped
Thanks
Scoty
:

 

[ITC]

Thank you very much for your help. Your advice worked perfectly to open the document I was looking for. However, I still run into a minor snag. Since my mail merge is set up with a parameter query, I still have about 5 more steps after opening the document before it is ready to print. I am not sure how to go about automating those steps. First, I need to press Alt and Tab to change applications in order to type in the parameter. Then I must enter the employees name. (All of this makes Access active again) so then I must reactivate Word and press the "view merged data" button.
Since these actions involve 3 different applications and user entry, I have no clue how to go about automating these processes.

 

[Scoty]

I think by changing the 1 to a 0 in the call statement that this automatically return the focus to access once the doc has been opened. then in the same code you will need to add a

DoCmd.GoToControl "EmployeeName"

then end sub. after that it is just a matter of adding the "back to Word" function in the after update event of the EmployeeName Field and doing sendkeys to tab to the "view merged data" button then the ~ to press enter. Automation is a wonderful thing but sometime the most simple premiss is the best. You still may want to consider making you merge a report that can be viewed and printed. Just a thought
Scoty
:

 

[adstam]

I our company we had more issues to solve with our documents created from access:
[ul][li]We have to use corporate designed templates[/li];
[li]We (sometimes) need to add text to the document after creating it;[/li]
[li]for some of our facility-services we have to save each document as it was printed.[/li][/ul]
Therefore i use a word-template that can be used with a word macro as well, in fact (see my code) i do use the code as in a Word macro. To jump to the places where variables must be used i use Bookmarks. And YES, this code is extreme user friendly, with one push on the button you create your Word document.

Sample code with only one bookmark:

Dim WordObj as Object
Set WordObj = CreateObject("word.application&quot
WordObj.Documents.Add Template:="mytemplate.dot"
WordObj.Goto Name:="mybookmark"
WordObj.TypeText Text:="you can use your access variable at this place"
WordObj.ActiveDocument.SaveAs Filename:="MyNewFile"
WordObj.ActiveDocument.Close
Set WordObj = Nothing

Now your document is reade and can be opened in the way Scoty described.

Ad

 

[ITC]

Ad,

I really hope that I am misunderstanding what you have suggested. Since I am not familiar with VBA, I tried typing in your suggestion so that I may step through it for better understanding. This created a huge mess in my Access application. First it would not let me step through, then it would not delete and also caused havoc on existing forms and reports. I have been able to clean up the mess but I am very disconcerted about why it happened in the first place.
If anyone can explain to me why this might have happened, please let me know.
Thanks
Viska

 

[Katerine]

Hello ITC,
I've read this thread and I'm afraid I'm still a little puzzled on what exactly you'd like. It sounds to me as if you'd like the user to be able to do the following (and please correct me if this is incorrect):

- Open a Word file and be prompted for an employee's name.
- See the finished (merged) product in Word.

If this is the case, I personally would go about this by doing all of the coding in Word.

To do this, begin by setting a reference in Word to the DAO library, so that you can manipulate Access objects from within Word.

Then have Word prompt for the employees name, using an input box, and save it in a string variable.

Then code an SQL statement which will query the Access database (which, correct me if I'm wrong.. need not even be visibly opened, just queried behind-the-scenes), and hold the results in a recordset variable in Word.

Then iterate through the recordset and set the mail merge data fields based on the recordset.

Then run the merge and display the results.

In short, it can be done, but it would be a fairly major undertaking (at least a couple days of coding, if you're not familiar with either Word or Access VBA). I can walk you through the "query the Access database" part of it, but somebody else would have to help with the "create the mail-merge fields and merge the file" part, as I'm far more familiar with Access than with Word VBA.

Please let me know if you'd like any further help/clarification on this (I know my writing can be kind of confusing sometimes.. )

 

[ITC]

Thank you everyone for all of your help. I have taken bits and pieces of everyones advice, and I think I have a working model. Ironically, it was as simple as a few command buttons. (Of course it took a little finessing to get it just right, but that is where I started.)
Katerine,
You seem to have wonderful advice, but I really wanted to start from Access, because that is where my users will be working when they need to get to the letters.
Thanks Again, You were all wonderful help!!!!!!!*:->*

 

[adstam]

Hi ITC,

I'm very sorry that my code brought you a lot of work. Because you qualify as programmer I assumed you are familiar with VBA. The given code was suggested AS PART of a more complete program and the quoted part like "My... where samples where you could have given your own names for documents, variables etc.

I really can not understand why things got messed up and in fact, I'm curieus about the reason this could happen with the given code.

Again, sorry, sorry, sorry...... :-(

Ad