How to set Replacement Text to Bullet Format when creating WordDoc from Excel using VBA

Question

Hello and thank you in advance.

I am creating a very complex Word document out of an Excelfile using VBA. It shall be possible to activate something and a text written in a cell shall be transfered to the word document. I got that already done. But at some parts it must be in a Bullets-Format. Right now I use tags like "<< replacementPoint1 >>" in the Word Template, find and replace them with the ReplacementText using something as easy as that one-line-code:

With WordDoc.Content.Find
   .Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With

But how can I set the replacementText to a bullet or a number like this:

• ReplacementText some more text here......

  1. ReplacementText some more text here.....

Answer 1

There are a number of possible approaches. One would be to not use wdReplaceAll - instead, the code would do one "find" at a time, apply the required formatting, then loop the find/replace and format again and again until nothing more is found. There are lots of examples of this approach here on Stack Overflow and on the Internet, in general.

Quicker would be to leverage the fact that Word can apply certain kinds of formatting as part of the Replace functionality. In the Word UI, press Ctrl+H to view the Find & Replace dialog box; click "More" then click "Format" to see the possibilities. Bullets and Numbering is not a selection, here... But Styles are.

If you're working with a template to generate these documents (highly recommended) then define the bullets / numbering to be used as styles in the template. If no template is used, the code can create the style definition(s) on-the-fly.

Specify the Style name (case-sensitive!) as part of the Replacement properties defined for the Find and set the Format property to True. Something like:

Dim rngFind as Word.Range 'Object if late-binding
Set rngFind = WordDoc.Content
With rngFind.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Replacement.Style = "My style"
  .Format = True
  .Execute FindText:=ReplacementTextF, ReplaceWith:=ReplacementText, Replace:=2
End With