run a Word macro and close through Access button

[Guest_imported]

I am developing a command button on an Acess form that will open Word, mail merge the query infomation, print, and close out of Word. all without the user seeing any of this.

I have taken 2 approaches to this task.

1. use the shell function (found on this site) to open Word, the file, and the marco. this works, but i cannot figure out how to close Word once this is complete. I also set the Shell statement to vbHdie, but i can still see Word when i run this.

2. use the Dim Object varaible to open Word and the file. Hide Word, and close out of the File and Word. My problem here is that i cannot figure out how to run the macro from access once the file is open.

Thank you for your assistance.

 

[kw2oak]

I have done this before in Access '97 - assume its same in 2K and XP:

ObjWord being my word object.
objWord.Run MacroName:="ProduceLetter"

I am having a related problem to you - I am opening as word doc, filling in some text at a bookmark or two and then I want Word to stay open so that the user can edit my document - I can do this fine but next time I call word my bookmark code executes but does not place anything on the document! - If I close my database, run my code again it works again? - any suggestions

 

[Rock6431]

jm1219:

The Macro should be removed from Word and entered as code directly with the Access routine.

This is done with ease. Here's a sample of my e-mail program which uses Access as the base suppliment and accesses Word for the users to type/create their e-mail. A custom toolbar in Access, closes the Word App and e-mails the word document.

The Basic's are to take the macro text information out of Word and simply add the Object name (in Access) in front of each command that appears in Word.

Word Macro --------------------
Visible = True
Application.Move Left:=0, Top:=0
Application.Resize Width:=640, Height:=480
Application.Caption = "E-Mail Reply"
-------------------------------

Access Coding -----------------
Set oApp = CreateObject("Word.Application&quot
oApp.Visible = True
oApp.Application.Move Left:=0, Top:=0
oApp.Application.Resize Width:=640, Height:=480
oApp.Application.Caption = "E-Mail Reply"
-------------------------------

----- And So On.....
Below is the Open routine using a Custom Template...
---------------------------------
oApp.Documents.Add Template:= _
"C:\winnt\CustomTemplateName.dot", NewTemplate:= _
False
oApp.ActiveWindow.WindowState = wdWindowStateMaximize
doc1 = oApp.Application.Caption

' Once done turn off Access Hourglass
DoCmd.Hourglass False
---------------------------------

So, All you have to do is record your own custom macro in Word using the VB Toolbar, and then take the text from that macro and insert it into the Access coding using a Word.Object as the pointer. Make sure to close the object whenever you no longer need access to it or you'll rack up a hoard of WinWord.exe/Word.exe in your taskmanager.

The above also applies for PPT and XLS as well. Complete automation.

Hope this helps...

Rock6431

 

[gcupstid]

"2. use the Dim Object varaible to open Word and the file. Hide Word, and close out of the File and Word. My problem here is that i cannot figure out how to run the macro from access once the file is open."

Could you post a sample of how this is done? Much appreciated!!!

 

[ProNetGroup]

Yes, I was wondering the same thing, if someone could help me out with a sample of how the macro would be written in the code window in access...or can you just call it from word?

I have already created the macro in word and have the code, but I'm not sure how to make it work properly. I am using the two FAQs about this very topic. At first, I just started with trying to get word to run and open my mail merge document. After that, I could manually just hit "merge" to get the information into the letter. Now I have recorded a macro in word that basically hits the "merge to document" button and then hits print, but access can't find the macro that I refer to in the code. The way that FAQ181-28 describes it just isn't working for me. Does anyone know what I am doing wrong??? The FAQ name is "Shell to Word, Open a Doc and run a Macro".

Thanks!