VBA - Delete Bookmark Text in Word based on Excel cell values

[Starol2]

Hi

New to VBA and trying to find a code that will

Open an Word Template document from within Excel (creating a new doc)
(The template document is populated with Bookmarks)
Remove bookmarks (and related text) depending on cell value

So if Excel column A and B has

Column A
Bookmark1
Bookmark2
Bookmark3
etc.

Column B
0
1
1

Where 0 deletes bookmark and paragraph in word template document
So in this example Bookmark1 would be deleted
Leaving both word and excel files open (word in view)

Thank you x

 

[Macropod]

You could use code like:

Sub CreateWordDocument()
'Note: This code requires a VBA reference to Word, via Tools|References
Dim xlSht As Worksheet, wdApp As New Word.Application, wdDoc As Word.Document, lRow As Long, r As Long
Set xlSht = ActiveSheet: lRow = xlSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
With wdApp
  'Show the Word session
  .Visible = True
  ' create a new Word Document based on the specified template
  Set wdDoc = .Documents.Add("C:\MyTemplate.dotm")
  With wdDoc
    'loop through used range and process the corresponding Word bookmarks
    For r = 1 To lRow
      If .Bookmarks.Exists(xlSht.Range("A" & r).Text) Then
        'Erase designated bookmark content
        If xlSht.Range("B" & r).Value = 0 Then
          .Bookmarks(xlSht.Range("A" & r).Text).Range.Text = vbNullString
        End If
      End If
    Next
  End With
  .Activate
End With
'MEMORY CLEANUP
Set wdDoc = Nothing: Set wdApp = Nothing: Set xlSht = Nothing
End Sub

 

[Starol2]

Woah, this is fab. Thank you so much Paul. Works like a dream.