How to pass Excel variable from macro to Word macro in VBA
My goal is to use a cell reference to search for a string of text in a cell by opening a document of a specific word and passing the cell or string to a search macro located in the VBA word. Basically I am trying to pass a variable from excel macro to word macro. I cannot make this work. My goal is to pass a variable Party
to a Word macro named macro5 . I can get the below code to work without going into an argument and just hardcoding on the text in the word macro, but it doesn't work when I try to pass the excel argument. Any help would be greatly appreciated.
My excel macro code:
Sub Open_Correct_WordDOC()
' Open_Correct_WordDOC Macro
Dim WordApp As Object
Dim WordDoc As Object
Dim Party As String
Party = "commercial"
MsgBox Party
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Open(Filename:="J:enterdocumenthere.docx", _
ReadOnly:=True)
WordApp.Visible = True
WordApp.Run "Normal.NewMacros.Macro5", Party
Set WordDoc = Nothing
Set WordApp = Nothing
End Sub
Word Macro Code:
Sub Macro5(xlvar As String)
Selection.Find.ClearFormatting
With Selection.Find
.Text = xlvar
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.Find.Execute
End Sub
source to share
In excel module:
Option Explicit
Public WordApp As Object
Public WordDoc As Object
Public Party As String
Sub Open_Correct_WordDOC()
' Open_Correct_WordDOC Macro
Party = "commercial"
MsgBox Party
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Open(Filename:="H:\docsearch.docx", ReadOnly:=True)
WordApp.Visible = True
WordApp.Activate
WordApp.Run "Macro5", Party
Set WordDoc = Nothing
Set WordApp = Nothing
End Sub
I think your problem was that you declared objects and string inside your excel macro. I made them public and the code works fine for me.
Addon: The Word macro is located in a module under the Normal template. This works great for me.
source to share