Word Function returning Run Time Error '438 when called in Excel

[Mozzie25]

Hi Everyone

I have been creating a macro in excel that will pull information from an excel sheet and insert into a word document.

After much trial and error I have managed to get it to insert all the information I want but I am now stuck on changing the formatting of what is inserted.

After trying a number of different ways to change the formatting inside the macro (none of which worked) I settled on creating a number of functions in word VBA to make the formatting changes I wanted (I.E Change to a style, bold or format to bullet points). These functions work in word with zero problems. But whenever I call them from the excel macro I get a Run-time error '438' Object doesn't support this property or method. I double and triple checked I have the word object library ticked, at this stage I'm assuming I'm doing something an excel object doesn't like but for the life of me I can not figure out where the issues is.

Here is a small section of the excel macro, if I run it without calling the word function it works fine. I have tried putting the call inside a with wrdApp with no luck. I also tried pulling it outside of the with wrdDoc but that didn't work either.

Set wrdApp = CreateWord
Set wrdDoc = wrdApp.Documents.Add
With wrdDoc
numbsheets = Application.Sheets.Count
.Content.ParagraphFormat.SpaceBefore = 0
.Content.ParagraphFormat.SpaceAfter = 0

.Content.InsertAfter "Internal Wiki"

Call wrdApp.cntrl("Internal Wiki", "Style", "Title")

.Content.InsertParagraphAfter
.Content.InsertParagraphAfter

Here is the cntrl word function

Public Function cntrl(txt As String, fnctn As String, optn As String, Optional optnsize As Integer) as Object
'
' A function to control the word functions from excel
'
'
Dim myRange As Range

Set myRange = fndtxt(txt)

If fnctn = "Style" Then
    Call Style(myRange, optn)
ElseIf fnctn = "List" Then
    Call List(myRange, optn)
ElseIf fnctn = "Format" Then
    If IsMissing(optnsize) Then
    Call format(myRange, optn)
    Else
    Call format(myRange, optn, optnsize)
    End If
End If
End Function

The fnd txt function

Public Function fndtxt(txt As String) As Range
'
' A function to find text and return it as a range. To be used in combination with the formatting funcitons
'
'
Set fndtxt = ActiveDocument.Range
With fndtxt.Find
 .text = txt
 .Forward = True
 .Execute
End With
End Function

And the style function.

Public Function Style(txt As Range, stylename As String) As Object
'
' A function to apply styles to ranges
'
'
Dim myRange As Range

Set myRange = txt
myRange.Style = stylename

End Function

I split them out into individual functions so I could use them separately if I wanted or together in the control function. I am sure this is not the most efficient way but after working on this for 3 days straight I needed to split things up or I was going to have an aneurism. To be through I tried them as sub's instead of functions and got the same error.

Sorry if this has been answered, I had a look through the forums but could not see anything like this.

Would appreciate any and all help this is driving me insane.

 

[Mozzie25]

Just to add as I can't seem to edit the question. I get the same error for all the formatting functions, I just focused on one as this seemed the best way to simplify things and make it easier to explain . Quite happy to post those as well if required.

 

[RoryA]

Assuming those functions are in Word, you'd call them like this:

wrdApp.Run "cntrl", "Internal Wiki", "Style", "Title"

rather than:

Call wrdApp.cntrl("Internal Wiki", "Style", "Title")