troubleshooting Question

How to use bookmark in Word Vba?

Avatar of Shyamulee Das
Shyamulee DasFlag for India asked on
Microsoft WordVBA
31 Comments1 Solution277 ViewsLast Modified:
I want to use bookmark in MS word using vba.
There is an excel sheet to find words and replace in document. In the list there are expansion of number units (eg: Hz - hertz) but I want to ignore this to replace in my word doc if its after a number(eg: 90 Hz). If it is anywhere in text then it can replace with its expansion.
So I want to use bookmark whenever it finds "number" before the "unit" and the name of the bookmark can be "autounit_1" for the 1st time it finds then for 2nd "autounit_2" and so on.
Whenever the macro will  run it will ignore the bookmark and replace the rest.

My code: it is only replacing whatever is listed in excel. I want to use bookmark in this.

Sub Replace()

Dim xl As New Excel.Application
Dim wb As Workbook
xl.Visible = False
Dim ws As Worksheet
Dim SelectedFileItem As String
Dim FDialog As FileDialog
Set FDialog = Application.FileDialog(msoFileDialogOpen)
Dim lastRow
Dim find_text
Dim replace_text
Dim oCell
Dim r As Range
Dim afile
Dim ExcelWasNotRunning As Boolean
Dim num_chk
Dim bmRange As Range

Set bmRange = ActiveDocument.Bookmarks("autounit").Range

   With FDialog
   .Title = "Select a file"
   .AllowMultiSelect = False
   .InitialFileName = "D:\OneDrive - CACTUS\Documents"
   .Filters.Clear
   .Filters.Add "Excel files", "*.xlsx"
   
    If .Show = -1 Then
        SelectedFileItem = .SelectedItems(1)
        afile = SelectedFileItem
    Else
        MsgBox "You cancelled the operation"
     Exit Sub
    End If
    End With
    
On Error Resume Next
   
    If Err Then
        ExcelWasNotRunning = True
    Set xl = CreateObject("Excel.Application")
    End If
    
On Error GoTo 0
    With xl
        Set wb = xl.Workbooks.Open(afile)
        Set ws = wb.Sheets("sheet1")
        ws.Activate
     
    End With
    
    If ExcelWasNotRunning Then
        xl.Quit
    End If
    
lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
num_chk = "([{.,%,/,-}]" + "[0-9] )"

  For oCell = 2 To lastRow
      find_text = ws.Range("A" & oCell).Value
      replace_text = ws.Range("B" & oCell).Value
       find_text = "(" + find_text + ")"
        
        Set r = ActiveDocument.Content
        
        
        r.Find.ClearFormatting
        
        With r.Find
        Options.DefaultHighlightColorIndex = wdGray25
        .ClearFormatting
        .Replacement.ClearFormatting
        .MatchCase = True
        .Text = find_text
        .MatchWholeWord = True
        .Replacement.Text = replace_text
        .Replacement.Highlight = True
        .Wrap = wdFindStop
        .Execute Replace:=wdReplaceAll
        End With
        
    Next oCell
    
    MsgBox " Replacement Completed "
   End Sub
ASKER CERTIFIED SOLUTION
GrahamSkan
Retired
Join our community to see this answer!
Unlock 1 Answer and 31 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 31 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros