How to Pass an Excel Variable from a Macro to a Word Macro in VBA

Question

My goal is to use a cell reference to search for the string of text in the cell by opening up a certain word document and passing in the cell or string into a find macro located in word VBA. Essentially, I am trying to pass a variable from an excel macro into a word macro. I cannot make this work. My goal is to pass the variable Party to a Word macro called macro5. I can get the below code to work by not passing in an argument and just hardcoding in the text in the word macro, but it does not work when I try to pass in an argument from excel. 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

Why not just transfer your word macro in Excel? It will work the same way. You just need to replace or declare the msWord constants you use in your Word Macro. — L42
Interesting question. While @L42 's comment might provide a workaround, it does not solve the underlying issue. I can verify that the issue exists (when the Word macro is in the Normal template), in spite of the Word VBA help on Application.Run suggesting exactly the code posted. — chris neilsen

TAKL TAKL · Accepted Answer · 2014-11-20T06:13:47

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 the objects and string inside your excel macro. I made them public and for me the code works fine.

Addon: The Word macro is located in a module under the Normal template. This works fine for me.

How to Pass an Excel Variable from a Macro to a Word Macro in VBA

1 Answers