Return value from Word VBA to calling Excel VBA

Question

From within MS Excel I have VBA code which runs VBA code within MS Word. Everything works fine. I need to return a string value from Word, back into an Excel variable. Is this possible?

Some irrelevant background... I'm building a list of pdf files within Excel and need to extract values out of each of those pdf files. Word natively reads pdf, that's why I'm using it. I have a perfectly working python script that does it but no-one else in the organisation knows python and my manager isn't comfortable having code that only I understand. Which is fair enough!

Any hint would be appreciated!

You can use clipboard. Copy in Word macro and then paste in Excel from clipboard — Zac
– Zac, Commented Mar 27, 2020 at 16:25

Tim Williams · Accepted Answer · 2020-03-28 00:04:09Z

1

Excel:

Sub RunWord()

    Dim appWd As Object, res

    Set appWd = GetObject(, "Word.Application")

    res = appWd.Run("Tester")

    Debug.Print res

End Sub

Word:

Public Function Tester()
    Tester = "hi!"
End Function

answered Mar 28, 2020 at 0:04

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Return value from Word VBA to calling Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related