CODEWITHSUNDEEP
vbaparametersexcelargumentsms-word
Pixel Elephant
Pixel Elephant

Reputation: 21383

Calling a Word VBA Sub with arguments from Excel VBA

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.

Upvotes: 3

Views: 18984

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

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

Upvotes: 6

Related Questions