Control Word from Excel

Posted on 2009-02-12
Last Modified: 2012-05-06
I am trying to control Word from Excel. In particular I am trying to do a search and replace. However I keep getting an error on the line "With wrdDoc.selection.find". Any suggestions ?


Option Explicit

Const wdReplaceAll = 2

Private Sub Cmd_Create_Letters_Click()

'Now creates the word letters

Dim wrdApp As Word.Application

Dim wrdDoc As Word.Document

Dim i As Integer

Dim No_Of_Letters As Long

Dim Doc_Required As String

Dim Current_Path As String

Dim Full_Doc_Name As String

Dim Range_of_Names As Range

Dim Todays_Date As Date

Dim Todays_Date_as_String As Date

Dim Current_Year As Long

Dim Current_Month As Long

Dim Current_Month_String As String

Dim Months_as_Strings As Variant

Dim Current_Full_Name As String

Dim Current_Name_without_Salutation As String

Dim Current_Address1 As String

Dim Current_Address2 As String

Dim Current_Address3 As String

Dim Current_PostCode As String

Dim Top_Name As Range

'get todays date and convert to string

Months_as_Strings = Array("Jan", "Feb", "Mar", "Apr", "May", "jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Todays_Date = Format(Now(), "dd-mm-yy")

Current_Year = Year(Todays_Date)

Current_Month = Month(Todays_Date)

Current_Month_String = Months_as_Strings(Current_Month - 1)

Todays_Date_as_String = Current_Month_String & " " & Current_Year

MsgBox Current_Month & " " & Current_Year


'Get the number of documents to print

Set Range_of_Names = Sheets("Mail merge").Range("Names_To_Use")

No_Of_Letters = Range_of_Names.Rows.Count

Set Top_Name = Sheets("Mail Merge").Range("Top_Of_Names")

'Get the current path

Current_Path = ActiveWorkbook.Path

'Invoke the word object

Set wrdApp = CreateObject("Word.Application")

wrdApp.Visible = True

'Determine which document is required

Doc_Required = UCase(Trim(ComboBox2.Value))

Full_Doc_Name = Current_Path & "\" & Doc_Required & ".doc"

For i = 1 To No_Of_Letters

'read in the next name

Current_Full_Name = Top_Name.Offset(i - 1, 0).Value

'read in the first line of the address

Current_Address1 = Top_Name.Offset(i - 1, 1)

'read in the second line of the address

Current_Address2 = Top_Name.Offset(i - 1, 2)

'read in the third line of the address

Current_Address3 = Top_Name.Offset(i - 1, 3)

'read in postcode of the address

Current_PostCode = Top_Name.Offset(i - 1, 4)

'Open the main template

Set wrdDoc = wrdApp.Documents.Open(Full_Doc_Name)


'Put the name in the document



    With wrdDoc.Selection.Find

        .Text = "Name"

        .Replacement.Text = "Ho"

        .Execute Replace:=wdReplaceAll

    End With

Open in new window

Question by:KapTheHat
    LVL 76

    Accepted Solution

    Try this. If it doesn't help, tell us what the error message is.
        With wrdDoc.Range.Find
            .Text = "Name"
            .Replacement.Text = "Ho"
            .Execute Replace:=wdReplaceAll
        End With

    Open in new window


    Author Comment

    Looks to be working. I will finish of the rest of the routine to see what is happening and then let you know te outcome. Thanks for the help so far.



    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now