• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

Control Word from Excel

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 ?

Thks

Kaps
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
 
 
 
 
 
    wrdDoc.Selection.WholeStory
    With wrdDoc.Selection.Find
        .Text = "Name"
        .Replacement.Text = "Ho"
        .Execute Replace:=wdReplaceAll
    End With

Open in new window

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

Open in new window

0
 
KapTheHatAuthor Commented:
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.

Regards

Kaps
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now