'VBA Excel to Word Document: How to Find and Replace ALL
I'm using VBA to search for a given word (Ex, Cell A1) in a Word document, then replace that word in the Word doc with a value that corresponds to the cell beside it (cell B1).
Most instances of the words are only found once in the document, but I have some that repeat throughout. For these repeated ones, only the first instance is replaced and the rest aren't. I tried to find some solutions to this, but I've really only come across Excel-specific ones.
Sub ReplaceAnnual()
'
'
'Declare Variables as Objects
Dim oCell As Integer
Dim from_text As String, to_text As String
Dim WA As Object
Dim ws As Object
Dim wb As Workbook
Dim IntialName As String
Dim fileSaveName As Variant
'Open workbook and setting to the wb object for further use
Set wb = Workbooks.Open("C:\Pathway\Point_Estimates.xlsx")
'Set up Excel Application: sets currently open file as object
Set ws = Workbooks("Point_Estimates")
'Set up Word Application
Set WA = CreateObject("Word.Application")
WA.Visible = True
'Opens Pre-Saved Document & activates it to use
WA.Documents.Open "C:\Pathway\Filename.dotm"
WA.Activate
'Tells which cells to find and replace elements
For oCell = 2 To 75
from_text = Sheets("Values").Range("A" & oCell).Value
to_text = Sheets("Values").Range("B" & oCell).Value
'Specifies which application to work inside
With WA.ActiveDocument
Set myRange = .Content
With myRange.Find
.Execute FindText:=from_text, ReplaceWith:=to_text, Replace:=1
End With
End With
Next oCell
'Activate Excel Worksheet
ws.Activate
'Declare where to find numbers for naming file (e.g. 2020)
InitialName = "Point_Estimates_" & Range("B2")
'Prompt dialog box and save
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
End If
'
'
End Sub
It ends with a prompt to save the Excel file with a specified name. Any tips or help on tweaking this would be greatly appreciated!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
