Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Control Word from Excel

Posted on 2009-02-12
2
Medium Priority
?
387 Views
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 ?

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
Comment
Question by:KapTheHat
2 Comments
 
LVL 76

Accepted Solution

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

Author Comment

by:KapTheHat
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.

Regards

Kaps
0

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