Excel macro to find&replace in MSWord 
Author Message
 Excel macro to find&replace in MSWord

Hi

I have names and addresses and headings in Excel, and want to put them
into a pro-forma letter in Word, using a macro running from Excel.
The name and address works as intended,but I can't  'find and replace'
the word 'heading'.
My unsuccessful effort is shown below. The problem section is marked
with 'xxxxxxxxxxxxxxx'
-to work with Word 97
Any advice appreciated

Thanks

Tony

Sub OpenBlankWL()
On Error GoTo errorhandler

Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Set wdApp = New Word.Application

'Make the document  visible.
With wdApp
    .Visible = True
    .WindowState = wdWindowStateMaximize
End With

'select the document 'pro-forma.doc'
Set myDoc = wdApp.Documents.Open("d:\windows\desktop\slo\pro-forma.doc")

'put the address at the top of the page.
Set mywdRange = myDoc.Words(1)
With mywdRange
    .Text = "Name/Address from excel cells will go here"
End With

'find the word 'HEADING' in 'pro-forma.doc'
'and replace it with some text from excel
'XXXXXXXXXXXXXXXXXXXXXXXXX

With mywdRange

   Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "HEADING"
        .Replacement.Text = "Heading details from Excel"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
'XXXXXXXXXXXXXXXXXXXX

    myDoc.SaveAs ("d:\windows\desktop\slo\" & "XLCellvalue" & ".doc")

errorhandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub
.....................................................
pro-forma.doc
......................................................
Address1
Address2
Address3

Dear Sir
                             HEADING
Body text

Yours sincerely
........................................................



Wed, 02 Jun 2004 22:54:16 GMT  
 Excel macro to find&replace in MSWord
I've got this to work, and thought it might be useful to people.

I have an excel sheet with data

Fred smith |  21 greenacre road liverpool
John Jones |   22 MArtlett Rd liverpool

etc

I have a blank standard reusable letter saved as a word doc.

If I select a name in Excel and run the Excel macro, it opens a blank
letter in Word, Takes the name & address from the excel cells,
inserts the name and address to send it to in the standard letter
after the 58th word on the left-hand side, where the postman will see
it, puts the address-only as a heading to the letter, (By finding the
word 'address' and replacing it with the address)
Then it saves the letter by the selected name, eg 'fred smith.doc.'

It all works rather well, but needs a bit of trouble shooting.

One line I don't understand, marked xxx below.
I don't understand why selection in the following line refers to the
excel selection rahter than something in word, which could be 'John
Jones' and I don't understand why it works anyway since 'John Jones'
is nothing to do with the search term!

Quote:
>> With wdApp.Selection.Find  

Happy with success so far.
Happy christmas all

Tony

..............................

Sub OpenBlankWL()
On Error GoTo errorhandler
Dim Addr_Left As String         'The name and address of the property
Dim Addr As String              'The addres of the property
Dim Nname As String             'the name of the property
Dim wdApp As Word.Application   'an open instance of microsoft word
Dim myDoc As Word.Document      'an open instance of a word document
Dim mywdRange As Word.Range     '

Set wdApp = New Word.Application 'Actually opens word
With wdApp                      'Make Word visible and full screen
    .Visible = True
    .WindowState = wdWindowStateMaximize
End With
Set myDoc = wdApp.Documents.Open("d:\windows\desktop\slo\wltemplate.doc")
'you have to make sure the blank letter is available at this location.
Note it isn't a MSWord template; it is a blank  document - sorry for
confusion'
Set mywdRange = myDoc.Words(58)  'the address to send the letter to
will go after the 58th word

adrow = ActiveCell.Row          'gives the row of the selected excel
cell- the one with the property details required
Nname = Cells(adrow, 1)         'the cell in that row with name in it
Ac = ActiveCell

Addr = Cells(adrow, 2)          'the next cell along, containing
address

Addr_Left = Replace(Addr, ",", Chr(13)) 'this is the propertyaddress
only + carriage returns (only works properly in XL2000).Otherwise,
comment it out...

'This next actually puts the property name and address where it should
go on the letter
mywdRange.Text = " " & Nname & Chr(13) & " " & Addr_Left & Chr(13) &
Chr(13)

 wdApp.ActiveWindow.ActivePane.View.Zoom.Percentage = 60  ' this makes
it easier to see whats happening on the pc
 wdApp.ActiveWindow.ActivePane.VerticalPercentScrolled = 5 ' and can
be omitted
For nn = 1 To 60000000 'this just slows it down so you can see it
happening
Next nn ' and should be removed really
With wdApp.Selection.Find  'xxx IDont understand this line.
'Selection' is the contents of the selected excel cell.  Doesn't quite
make sense to me. XXX
    .Text = "PREMISES"  'It still works though. It finds the word in
caps, and replaces it with the address of the premises.
    .Replacement.Text = Addr
    .Forward = True
    Wrap = wdFindContinue
    .MatchCase = True  ' some of this in the next few lines might be
unnecessary
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
    End With

   myDoc.SaveAs ("d:\windows\desktop\slo\" & Nname & ".doc") ' make
sure that the folders exist - it wont work otherwise.
'   End With

errorhandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub



Sun, 06 Jun 2004 21:16:17 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. WORD X: VBA macro including find/replace with styles much slower than regular macro

2. Running a word Macro & an excel macro

3. Visio macro to find and replace text

4. Felp me on Find and Replace Macro

5. Find/Replace Macro

6. Find/Replace Macro for Forms

7. Find and replace in headers with a macro

8. need help with (simple?) find/replace macro

9. Help with complex find and replace macro.

10. Macro - Find Search and Replace value

11. Search & replace not being done by macro

12. Excel VBA: Find in files, Search, Replace

 

 
Powered by phpBB® Forum Software