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

      

+3


source to share


1 answer


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.

0


source







All Articles