Calling a Word VBA Sub with arguments from Excel VBA

Question

I have a very simple Word sub in a dotm template:

Sub YHelloThar(msg As String)
    MsgBox (msg)
End Sub

I then have an Excel sub:

Sub CallWordSub()
        Dim wdApp As Word.Application
        Dim newDoc As Word.Document

        'Word template location   
        strFile = "C:\Some\Folder\MyWordDoc.dotm"
        'Get or create Word application
        Set wdApp = GetObject(, "Word.Application")
        If wdApp Is Nothing Then
            Set wdApp = CreateObject("Word.Application")
        End If
        'Create new Word doc from template
        Set newDoc= wdApp.Documents.Add(strFile)
        'Call the YHelloThar sub from the word doc
        Call wdApp.Run(strFile & "!YHelloThar", "Hello")
    End If
End Sub

The last line gives me "Run-time Error '438': Object does not support this property or method."

I'm not sure what I am doing wrong - everything I have looked up indicates this is the proper way to do call subs from different applications.

Furthermore, if I change the last line to be a parameter-less call it suddenly works correctly.

Siddharth Rout · Accepted Answer · 2012-03-02T16:02:37.947

6

TRIED AND TESTED

Call wdApp.Run("YHelloThar", "Hello")

Also you have an extra End If at the end. A typo I guess?

TIP: To avoid runtime errors, you will have to handle error just before calling

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

FOLLOWUP OF MY TIP

Here is an example. Also I have used Late Binding so that it will work with every Office Version.

Sub Sample()
    Dim wdApp As Object, newDoc As Object
    Dim strFile As String

    strFile = "C:\Some\Folder\MyWordDoc.dotm"

    '~~> Establish an Word application object
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
        Set wdApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0

    wdApp.Visible = True

    Set newDoc = wdApp.Documents.Add(strFile)

    Call wdApp.Run("YHelloThar", "Hello")

    '
    '~~> Rest of the code
    '
End Sub

edited Mar 02 '12 at 16:02

answered Mar 02 '12 at 15:54

Siddharth Rout

147,039
17
206
250

Awesome. This worked perfectly. Special thanks for the extra comments regarding error checking and Late Binding. I've been kinda avoiding Late Binding as I'm very new to VBA and like to see what properties/methods something has (which obviously doesn't work that well when everything is just an Object) but I probably should make the switch soon. – Pixel Elephant Mar 02 '12 at 20:38
Addendum: This also works for a Sub that isn't located in the active document, but in a .dotm template that is auto-loaded via Startup folder of Word. – Andre Aug 22 '18 at 14:24

Calling a Word VBA Sub with arguments from Excel VBA

1 Answers1