Hi Forum Community ? I am doing a find and replace using the cell values in Excel to replace the words in Word. I want to be able to make my replacement text all uppercase but I do not know how to do it. For example, if my replacement text is ws.Range("B" & currentRow).Value, I need a function to uppercase it other than manually changing it in the worksheet. This is because I do not want the information in the worksheet to be uppercased. I would really appreciate your help.
VBA Code:
Option Explicit
Public Sub WordFindAndReplaceSave()
Dim ws As Worksheet, msWord As Object
Dim currentRow As Long
Dim rowCount As Long
Dim lastRow As Long
Dim filename As String
Dim Path As String
Dim building As String
Dim finish As Integer
Path = "C:\Edited Letters"
Set ws = ActiveSheet
Set msWord = CreateObject("Word.Application")
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).row
For currentRow = 2 To lastRow
If Not ws.Rows(currentRow).Hidden Then
filename = ws.Range("A" & currentRow).Value
building = ws.Range("F" & currentRow).Value
With msWord
.Visible = True
.Documents.Open "C:\LOR - STC.docx"
.Activate
With .ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "31 October 2017"
.Replacement.Text = ws.Range("B" & currentRow).Value
.Forward = True
.Wrap = 1 'wdFindContinue (WdFindWrap Enumeration)
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=2 'wdReplaceAll (WdReplace Enumeration)
End With
msWord.ActiveDocument.SaveAs filename:=Path & "/" & "LOR - " & filename & Space(1) & building & ".docx"
msWord.ActiveDocument.Close
rowCount = rowCount + 1
End With
End If
Next currentRow
msWord.Quit
finish = MsgBox("Automation of letter is done", vbOKOnly + vbInformation + vbDefaultButton1, "Generate Letter")
End Sub
