Help Required..Excel macro from Excel to Word

[naveenkumartalari]

I want to do this using excel vba macro.

I have to find specific text (say "abc") in a word doc and replace that with a range of cells say range("A5:A7") in the current active excel sheet.

I am badly stuck on this.Please help..!!!

Thanks
Naveen

 

[naveenkumartalari]

Test

 

[NdNoviceHlp]

Untested. HTH. Dave

Dim Wapp As Object, STRtoFind As String, Sourcefile As String
Sourcefile = "C:\testfolder\test.doc"
STRtoFind = "abc"
Set Wapp = CreateObject("Word.Application")
Wapp.Documents.Open FileName:=Sourcefile, ReadOnly:=True
Wapp.ActiveDocument.Select
With Wapp.Selection.Find
.Text = STRtoFind
.Forward = True
.MatchWholeWord = True
.Replacement.Text = CStr(ActiveSheet.Range(A5))
.Execute , , , , , , , , , , 2 'wdReplaceAll
End With
Wapp.ActiveDocument.Close savechanges:=False
Wapp.Quit
Set Wapp = Nothing

 

[naveenkumartalari]

Thanks Dave..But I am not able to use more than 1 cell in the Range function while using with Replacement.Text
So Range(A5) is working however, Range("A5:A7") is throwing error

 

[NdNoviceHlp]

Well range A5:A7 is 3 seperate cells. What exactly due you want to replace "ABC" with? 3 seperate strings, 1 string containing the contents of the 3 cells, or something else? I don't think U can just insert a range of cells in the .doc to replace some text... where would it go/how would it fit? I've been wrong many times before though. Maybe abit more info about your desired outcome is needed. Dave

 

[s.ridd]

I don't think U can just insert a range of cells in the .doc to replace some text...

Hi Dave, technically you can just paste over a selection. I've made a quick demonstration of this sticking as close to your original code as possible.

Dim Wapp As Object, STRtoFind As String, Sourcefile As StringSourcefile = "C:\testfolder\test.doc"
Set Wapp = CreateObject("Word.Application")
Wapp.Documents.Open Filename:=Sourcefile
STRtoFind = "abc"
Wapp.ActiveDocument.Select
With Wapp.Selection.Find
    .Forward = True
    .Wrap = wdFindStop
    .Text = "Hello"
    .Execute
End With
Range("A5:A7").Copy
Wapp.Selection.Paste
Wapp.ActiveDocument.Close savechanges:=True
Wapp.Quit
Set Wapp = Nothing

I say technically because

Maybe abit more info about your desired outcome is needed

is definitely correct

 

[NdNoviceHlp]

Thanks s.ridd for the info. It inspired me to do a bit more learning. The code I posted was incomplete and just wrong. So here's the right code for a single cell.

Sub ReplaceWordText()
Dim Wapp As Object, STRtoFind As String, Sourcefile As String
Sourcefile = "C:\test\test.doc"
STRtoFind = "abc"
On Error GoTo Erfix
Set Wapp = CreateObject("Word.Application")
Wapp.Documents.Open Filename:=Sourcefile, ReadOnly:=False
Wapp.ActiveDocument.Select
With Wapp.Selection.Find
.Text = STRtoFind
.Forward = True
.MatchWholeWord = True
.Replacement.Text = CStr(Sheets("sheet1").Range("A" & 5))
.Execute , , , , , , , , , , 2  'wdReplaceAll
End With
Wapp.ActiveDocument.Close savechanges:=True
Wapp.Quit
Set Wapp = Nothing
Exit Sub

Erfix:
On Error GoTo 0
MsgBox "Error"
Wapp.Quit
Set Wapp = Nothing
End Sub

As far as copying the range and pasting it, it does work. It can be done by pasting at the .found location but I don't know why U would do it as it ruins the document format. So maybe we wait for more info about the desired outcome. Dave