PDA

View Full Version : From Excel Find Replace in Word 2



mprija
04-06-2009, 12:26 AM
Hello everybody.

Please help. I am not really sure if this is right part of forum, maybe I should post it under Word help, but my question is connected with Excel too. So guys do not judge me too harsh, I am a newbie in VBA codeing.

I have a Excel macro which opens MS Word document (orig.doc), searches in Word doc for words identical to words from Excel spreadsheet Cells (1, 1), (2, 1),...and if it finds words which are identical to words from (1, 1), (2, 1),... it replaces them with words from (1, 2), (2, 2),... In the end it saves changes to new documment (new.doc).

How to change bellow code that instead of writeing new line of code for each new row Call Change_Bookmark(Cells(1, 1).Value, Cells(1, 2).Value),........
, it would loop through spreadsheet rows (column A) till it will find empty cell.


Option Explicit
'the document
Dim Inv_doc As Object
'the application
Dim WD As Object
Sub Trans_2()
'where is the template located
Const which_document As String = "D:\macro\orig.doc"
'need an instance of word
Set WD = CreateObject("Word.Application")
WD.Visible = True
Set Inv_doc = WD.Documents.Open(which_document)
'*** code to manipulate your document
'replace the text in the document with text in cells
Call Change_Bookmark(Cells(1, 1).Value, Cells(1, 2).Value)
Call Change_Bookmark(Cells(2, 1).Value, Cells(2, 2).Value)
Call Change_Bookmark(Cells(3, 1).Value, Cells(3, 2).Value)
WD.Activate
Inv_doc.SaveAs "d:\macro\new.doc"
Inv_doc.Close
WD.Quit
Set Inv_doc = Nothing
Set WD = Nothing
End Sub
Sub Change_Bookmark(Template_Value As String, New_Value As String)
Dim oword As Object
For Each oword In Inv_doc.Words
If oword.Text = Template_Value Then
oword.Text = New_Value
End If
Next oword
Set oword = Nothing
End Sub

Bob Phillips
04-06-2009, 01:09 AM
Not tested



Option Explicit
'the document
Dim Inv_doc As Object
'the application
Dim WD As Object

Sub Trans_2()
Dim LastRow As Long
Dim i As Long

'where is the template located
Const which_document As String = "D:\macro\orig.doc"
'need an instance of word
Set WD = CreateObject("Word.Application")
WD.Visible = True
Set Inv_doc = WD.Documents.Open(which_document)
'*** code to manipulate your document
'replace the text in the document with text in cells
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
Call Change_Bookmark(Cells(i, 1).Value, Cells(i, 2).Value)
Next i
WD.Activate
Inv_doc.SaveAs "d:\macro\new.doc"
Inv_doc.Close
WD.Quit
Set Inv_doc = Nothing
Set WD = Nothing
End Sub

Sub Change_Bookmark(Template_Value As String, New_Value As String)
Dim oword As Object
For Each oword In Inv_doc.Words
If oword.Text = Template_Value Then
oword.Text = New_Value
End If
Next oword
Set oword = Nothing
End Sub