Calling a Word VBA Sub with arguments from Excel VBA
18,125
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
Related videos on Youtube
Comments
-
Pixel Elephant about 2 years
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.
-
Pixel Elephant over 12 yearsAwesome. 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.
-
Andre almost 6 yearsAddendum: 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.