Hi everyone, I am having some issues finding and replacing a word document from excel. If anyone could help me out I would greatly appreciate it.
I have the following code
Sub WordFindandReplace()
'word dims
Dim strReplace As String, strFind As String
Dim docNameStart As String, docNameSave As String
Dim wdApp As Word.Application, wdDoc As Word.Document
'necessary for error removal
Dim lngjunk As Long
Dim oshp As Shape
On Error Resume Next
'excel dims
Dim XLS As Excel.Application
Dim xlWs As Worksheet
Dim xlWs2 As Worksheet
Set xlWs = ActiveWorkbook.Sheets(1)
Set xlWs2 = ActiveWorkbook.Sheets(2)
Dim i As Long
Dim lastrow As Long
Dim rngstory As Word.Range
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row 'this version of last row looks in any column for the furthest down cell with data in it
ScreenUpdating = False
lngjunk = Word.ActiveDocument.Sections(1).Headers(1).Range.StoryType
'filepath goes here !Need to update when moving template!
docNameStart = "C:\Users\brandonkaplan\Desktop\!Compass Pricing Transparency Opportunity Overview TEMPLATE with RX BK DOC TEMPLATE.doc"
' 'to change to user chosen file name
' 'docNameStart = Application.GetOpenFilename(Title:="Please select a file")
Set wdApp = New Word.Application 'Creates new instance of word
Set wdDoc = wdApp.Documents.Open(docNameStart) 'opens word template
'insert pages depending on how many procedure locations there are
Dim ival As Integer ' count of procedures
ival = Application.WorksheetFunction.CountIf(Range("A1:A" & lastrow), "%%Location*%%")
If ival > 3 Then
Warning = MsgBox("Warning, more than 3 procedures!")
Warning = Warning & vbNewLine
Warning = Warning & MsgBox("Have you added extra pages in the word doc?", vbYesNo)
If Warning = vbNo Then Exit Sub
End If
For i = 2 To lastrow
strFind = xlWs.Cells(i, 1).Value
strReplace = xlWs.Cells(i, 2).Value
'should search through all rangestories and replace
For Each rngstory In Word.ActiveDocument.StoryRanges
Do
With rngstory.Find
.text = strFind
Replacement.text = strReplace
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll
End With
Set rngstory = rngstory.NextStoryRange
Loop Until rngstory Is Nothing
Next rngstory
Next i
ScreenUpdating = True
End Sub
Could anyone tell me what I am doing wrong?
It should be looping down column A for what to find, looping down column B for what to replace, and replacing no matter if its in a header or a text box or in the main document.
Thanks in advanc efor any help!
Bookmarks