CODEWITHSUNDEEP
excelvbams-word
David G
David G

Reputation: 2347

Writing bullet list in Word, from Excel VBA

I am adding words to a multi-line String strEvap in Excel. Some of the lines start with bullet points (the Bullet character •). Example string:

3 Evaporateurs
Évaporateur de type mural, modèle --, à installer et raccorder au condenseur, complet avec :
• Alimentation électrique par l’unité de condensation via un câble 14/3+Gnd
• Moteurs ECM

Once my string is complete, I create a Word document and write the string to the Word document using the following code:

Function FnWriteToWordDoc(strEvap As String, strCond As String)
   Dim objWord
   Dim objDoc
   Dim objSelection

   Set objWord = CreateObject("Word.Application")
   Set objDoc = objWord.Documents.Add
   objWord.Visible = True
   Set objSelection = objWord.Selection
   objSelection.TypeText (strEvap)
End Function

This works fine. At this point, I have a Word document with the string written in the same style as my example above.

MY QUESTION:

How do I format the lines that start with a bullet to be recognized as part of a bullet list in Word? Ideally it would be done as I write to the document but I don't mind if it goes over all lines and makes them a bullet list if applicable at the end.

So far I have the following piece of code that turns a selected line into a bullet list. It works on its own, but has to be used from the Word document, not my Excel code.

Sub RendreBulletDansWord()
    Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
        ListGalleries(wdBulletGallery).ListTemplates(1), ContinuePreviousList:= _
        False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:= _
        wdWord10ListBehavior
End Sub

Upvotes: 2

Views: 3489

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

Word has an AutoFormat functionality that can do this. AutoFormat can be applied to the entire document, or a Range. I recommend you write the content to a Range object, not a Selection, so that you can be sure of what's being affected.

AutoFormat can do a lot of things, so it's necessary to specify what you want and don't want. I've included only the option for bulleted lists, below. Conceivably, you might want to turn other options off. You can find the entire listing in the Word language reference. Note that there's AutoFormat as well as AutoFormatAsYouType settings. You don't need to worry about the latter, but pay attention because some of the AutoFormat settings are listed alphabetically after AutoFormatAsYouType.

Dim rng as Word.Range
Set rng = objWord.Selection.Range
rng.Text = strEvap
objWord.Options.AutoFormatApplyBulletedLists = True
rng.AutoFormat

*Although, if you use .TypeText it could conceivably work with AutoFormatAsYouType settings. But I personally prefer to not work with Selection and TypeText.

Upvotes: 2

Related Questions