Option Explicit
Sub FRandHyperlink()

'Macropod ©2012 from https://www.msofficeforums.com/word-vba/12803-find-replace.html#post34254
'gregmaxey © 2025 from https://www.msofficeforums.com/word-vba/53797-insert-hyperlink-text-replacement-after-find-replace.html

Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, wdDoc As Document
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim iDataRow As Long, xlFList As String, xlRList As String, xlHList As String, i As Long
Dim xlFItem As String, xlRItem As String, xlHItem As String
StrWkBkNm = "C:\Users\calista.hadiwarsito\Documents\Simplifikasi PDR\Macro Testing\Input Template Tester.xlsm"
StrWkSht = "Data Input"
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
On Error Resume Next
'Start Excel
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
  MsgBox "Can't start Excel.", vbExclamation
  Exit Sub
End If
On Error GoTo 0
With xlApp
  'Hide our Excel session
  .Visible = False
  ' The file is available, so open it.
  Set xlWkBk = .Workbooks.Open(StrWkBkNm, False, True)
  If xlWkBk Is Nothing Then
    MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
    .Quit: Set xlApp = Nothing: Exit Sub
  End If
  ' Process the workbook.
  With xlWkBk
    'Ensure the worksheet exists
    If SheetExists(xlWkBk, StrWkSht) = True Then
      With .Worksheets(StrWkSht)
        ' Find the last-used row in column A.
        iDataRow = Sheets("Data Input").Cells(.Rows.Count, "D").End(xlUp).Row ' -4162 = xlUp
        ' Capture the F/R data.
        For i = 1 To iDataRow
          ' Skip over empty fields to preserve the underlying cell contents.
          If Trim(.Range("D" & i)) <> vbNullString And Trim(.Range("E" & i)) = "FR" Then
            xlFList = xlFList & "|" & Trim(.Range("D" & i))
            xlRList = xlRList & "|" & Trim(.Range("H" & i))
            xlHList = xlHList & "|" & Trim(.Range("I" & i))
          End If
        Next
      End With
    Else
      MsgBox "Cannot find the designated worksheet: " & StrWkSht, vbExclamation
    End If
  .Close False
  End With
  .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Exit if there are no data
If xlFList = "" Then Exit Sub

    Set wdDoc = Documents.Open(ThisWorkbook.Path & "\Template PDR 3_full.docx", AddToRecentFiles:=True, Visible:=True)
    'Process each word from the F/R List
    With wdDoc
    Options.DefaultHighlightColorIndex = wdYellow
    End With
      With wdDoc.Range.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Replacement.Highlight = True
        .MatchWholeWord = True
        .MatchCase = True
        .Wrap = wdFindContinue
        For i = 1 To UBound(Split(xlFList, "|"))
          xlFItem = Split(xlFList, "|")(i)
          xlHItem = Split(xlHList, "|")(i)
          xlRItem = Split(xlRList, "|")(i)
          If xlHItem <> "" Then
            .Text = xlFItem
            While .Execute
                wdDoc.Hyperlinks.Add Anchor:=wdDoc.Range, _
                Address:=xlHItem, _
                ScreenTip:="", _
                TextToDisplay:=xlRItem
            Wend
          Else
            .Text = xlFItem
            .Replacement.Text = xlRItem
            .Execute Replace:=wdReplaceAll
          End If
        Next
      End With
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub