Learn how to a build a cloud-first strategyRegister Now


Control Word from Excel

Posted on 2009-02-12
Medium Priority
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

GrahamSkan earned 200 total points
ID: 23623999
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

ID: 23625098
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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