CODEWITHSUNDEEP
excelvbams-word
user18349414
user18349414

Reputation: 51

Compile Error: Argument Not Optional on Find in a Word document using Excel VBA

I have Excel_Doc_A and Word_Doc_A.

Excel_Doc_A will use VBA to:
-refer to Word_Doc_A,
-find certain text values (in this example, "TITLE"),
-insert a hyperlink into that text field,
-change the text to what is indicated in Excel_Doc_A,
-save the Word_Doc_A as a new file Word_Doc_B, that has all the text replaced and hyperlinks included.

I get

Compile Error: Argument Not Optional on Find

on the line

Set myFind = myRange.Find

Public Sub testing_1()

Dim documentApplication As Word.Application
Dim documentDocument As Word.Document

Set narApplication = CreateObject("word.application")
Set narDocument = narApplication.Documents.Open(ThisWorkbook.Path & "/document_template.docx")

Dim TITLE As String
Dim myRange As range
Dim myFind As Find
Dim filePath As String

TITLE = range("B1")

'For each value, find it's value in the blankdocument
Set myRange = narDocument.Content
Set myFind = myRange.Find
With myFind
    .Text = "__TITLE__"
    searchResult = .Execute
    .Replacement.Text = TITLE
End With

narDocument.Hyperlinks.Add Anchor:=myRange, Address:="http://www.google.com", TextToDisplay:="__TITLE__ " 
    
filePath = ThisWorkbook.Path & "/document_test.docx"
narDocument.SaveAs2 Filename:=filePath

'Cleanup
narDocument.Quit False
Set narApplication = Nothing
Set narDocument = Nothing

End Sub

Upvotes: 0

Views: 276

Answers (1)

BigBen
BigBen

Reputation: 50067

Dim myRange As Range is implicitly As Excel.Range. You get a compile error because Set myFind = myRange.Find is referencing the Excel Range.Find method. You need members from the Word object model.

Early-binding:

Dim myRange As Word.Range
Dim myFind As Word.Find

Late-binding:

Dim myRange As Object
Dim myFind As Object

It would be best to be consistent.

Early-binding:

Dim documentApplication As Word.Application
Dim documentDocument As Word.Document
Set narApplication = New Word.Application

Late-binding:

Dim documentApplication As Object
Dim documentDocument As Object
Set narApplication = CreateObject("word.application")

Upvotes: 2

Related Questions