Get return value of Word macro function called from Excel macro

Question

I am doing an Excel macro to get the text of the 1st paragraph preceding the 1st table (basicly the title of the table) of a Word file.

My macro from Excel is the following :

Sub Main()

    Set applicationWord = CreateObject("Word.Application")
    Set documentWord = applicationWord.Documents.Open(Filename:="C:\Users\...\my_word.docm", ReadOnly:=True)
    applicationWord.Visible = True
    applicationWord.Activate
    applicationWord.Run "GetFirstString"

    Set documentWord = Nothing
    Set applicationWord = Nothing

End Sub

We can't write str = applicationWord.Run "GetFirstString" as any other programming language to save the return String in a variable. And in my Word file my_word.docm, I have the following macro GetFirstString :

Function GetFirstString() As String

    GetFirstString = ActiveDocument.Tables(1).Range.Previous(Unit:=wdParagraph, Count:=1)
    
End Sub

This is a prototype. I want to do it like that because I want to write an Excel file with the text extracted from each Word file of a specified folder. I tried the following macro from Excel to see if I was able to read text from Word file using Paragraph object but I got an error, this code is not working with Excel VBA, who doesn't recognize method members.

Private Sub ExtractionPrototype()

    Dim applicationWord As Object
    Dim documentWord As Object

    Set applicationWord = GetObject(, "Word.Application")
    applicationWord.Visible = True
    Set documentWord = applicationWord.Documents.Open(cheminDossierReparation)
    
    MsgBox documentWord.Tables(1).Range.Previous(Unit:=wdParagraph, Count:=1)

    documentWord.Close savechanges:=False

End Sub

Is there any way to return values from a Word macro to an Excel macro ? It is written in the doc that "The Run method returns whatever the called macro returns" but what is exactly this value and how to get it ?

Timothy Rylatt Timothy Rylatt · Accepted Answer · 2020-08-25T13:18:09

The only reason that your ExtractionPrototype routine didn't work is because you have used the constant value wdParagraph, part of the WdUnits enum, which isn't defined in Excel. Either substitute it with its value, which is 4, or declare it as a constant with that value in your routine.

"We can't write str = applicationWord.Run "GetFirstString""

Correct, because you've missed the brackets that tell VBA you expect a return value. So you write str = applicationWord.Run("GetFirstString")

Get return value of Word macro function called from Excel macro

1 Answers