Word Macro - Running one from Access?

[meandmyhorse]

Seems like everyone is trying to figure out how to automate mail merge from Access to Word.

I am one of those. I have very limited knowledge. I have gleamed all that I can from the board, and have just one last idea.


I can get on the click of a button Access to open Word and launch by form letter. But haven't been able to get mail merge to work.

I wrote a Macro in Word that works, but I can't figure out the code to type in Access that once it opens Word and the form letter to then run the macro in Word.

I did a docmd.runmacro "merge", but it is looking at Access for the macro and the macro is in Word.

There must be a way? I think I could make the macro run when the document is opened, but I would like to stay away from that if I can.

Any help would be appreciated. Thanks!

 

[Travis]

See Microsoft Knowledgebase article:

ACC: Running a Microsoft Word 97 Macro Using Automation
ID: Q160294

 

[Drevlin]

I've found that the following code will allow you to copy and paste your vba macro directly from Word (or Excel) into Access and just by adding "." in front of the applications members will run the macro.

So say you have a macro that looks like this:

Selection.WholeStory
Selection.Font.Name = "Arial Black"
Selection.Font.Size = 15
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "Hello"
.Replacement.Text = "Goodbye"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll


You would paste it into the following code:

Dim wd As Word.Document
Dim strFile As String

strFile = "C:\My Documents\Dear HP Service Center Worker.doc"

Set wd = GetObject(strFile)
wd.Windows(1).Visible = True

With wd.ActiveWindow 'with excel it would be wd.Parent
'copy and paste macro from Word here

End With

Then all you have to do is put in the "." before each of the items so your final code would look like this:

Dim wd As Word.Document
Dim strFile As String

strFile = "C:\My Documents\MyDoc.doc"

Set wd = GetObject(strFile)
wd.Windows(1).Visible = True

With wd.ActiveWindow
'copy and paste macro from Word here
.Selection.WholeStory
.Selection.Font.Name = "Arial Black"
.Selection.Font.Size = 15
.Selection.Find.ClearFormatting
.Selection.Find.Replacement.ClearFormatting
With .Selection.Find
.Text = "Hello"
.Replacement.Text = "Goodbye"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
.Selection.Find.Execute Replace:=wdReplaceAll
End With


It will be as if your running the macro from Word.

Anyway, this works for me.
as a note: I didn't do error checks to make sure the file wasn't already open

I hope this helps somebody.

 

[Susan Owen]

"I've found that the following code will allow you to copy and paste your vba macro directly from Word (or Excel) into Access and just by adding "." in front of the applications members will run the macro...Anyway, this works for me.
as a note: I didn't do error checks to make sure the file wasn't already open

I hope this helps somebody."

Hi Drevlin, thanks a million for posting this! It is just exactly what I needed! I'm testing it now...

 

[Susan Owen]

Function RunWordMacro()
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Open _
("C:\My Documents\Wordtest.doc")
WordApp.Visible = True
WordApp.Run "Macro1"

' Uncomment the next line of code to print the document.
' WordDoc.PrintOut Background:=False

' Uncomment the next line of code to save the modified document.
' WordDoc.Save

WordApp.Quit SaveChanges:=wdDoNotSaveChanges
Set WordApp = Nothing
End Function


Drevlin's code really helped. The above I got from the article, then I had to adapt it as follows to get it to work:

Function RunWordMacro()
Dim WordApp As Object
Dim WordDoc As Object
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Open _
("M:\DB-Projekten\Zeichnungs-DB-Marine\Transmittal-Seriendok.doc")
WordApp.Visible = True
'WordApp.Run "Macro1"
' Uncomment the next line of code to print the document.
'WordDoc.PrintOut Background:=False
' Uncomment the next line of code to save the modified document.
'WordDoc.Save
'this line of code causes a compile error:
'WordApp.Quit SaveChanges:=wdDoNotSaveChanges

'***TESTING MACRO COMMANDS HERE:

With WordApp.ActiveWindow
'copy and paste macro from Word here
.Selection.WholeStory
.Selection.Font.Name = "Arial Black"
.Selection.Font.Size = 15
.Selection.Find.ClearFormatting
.Selection.Find.Replacement.ClearFormatting
With .Selection.Find
.Text = "Hello"
.Replacement.Text = "Goodbye"
.Forward = True
'.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
'.Selection.Find.Execute Replace:=wdReplaceAll
End With

Set WordApp = Nothing
End Function

***

And it did work! Thanks so much for the help! This is great to be able to run word macros from access!

 

[Susan Owen]

Winword Macros

The statements that have "wd" in them usually do not work. Is there some list of which commands will work and which will not?

 

[Drevlin]

The "wd" words are Constants. Make sure you have a reference to Microsoft Word set.
Tools-> References -> Microsoft Word X.0 Object Library

Otherwise just figure out what the Constant is equal to in Word and switch it out in Access (you can do this by typing Debug.Print wdConstant in the immediate window.)

For Example:

debug.Print wdReplaceAll

would return:

2


But creating the reference should solve the problem with much less hassle.

 

[Susan Owen]

Constants

Thanks Drevlin! Duh, I guess I just showed what a novice I am. Well, anyway, I guess I have to learn this a little bit at a time. I've used the bookmark bit that jwindon posted here:

http://www.access-programmers.co.uk...threadid=20120&highlight=merge+bookmarks+word

And it does the trick, but I'm glad to know how to do it "right". I kept getting the error something like "this function is not supported" and began to wonder if I was trying to do the impossible, if these things just weren't supported by my access program or something.

I did set the reference as you said, and I didn't get a compile error, so far so good. I ran a function with a "wd..." reference, and it did actually work, so thanks!

 

[sillykid]

I can't click References under Tools. How do I fix this?

 

[Thales750]

move to top