Removing Bullets from a Word Document

[Vis_Bas_App]

Hello Everybody,

I am new to this board and have my first question for which your help would be greatly appreciated.

I am developing a tool using VBA which transfers the contents of cells in a Word table to cells in an Excel Spreadsheet.

The tool is almost complete but there is a problem in that the document contains bullet points. I need to remove these bullet points from the word document and replace them with semi-colons before transferring the string data to the Excel cells.

I know that there is a way of getting rid of all bullet points in a Word document but this is not sufficient as the lists still need to be seperated in some way (i.e semi-colons).

I have looked on numerous web sites and tried many different ways of tackling this Unicode related issue (using the ChrW function mainly).

I wrote the following function in an attempt to replace bullet points with semi-colons but it is not working:

Private Sub Find_Unicode()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim MyString As String

Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open("C:\Bullets.doc")

wrdApp.ActiveDocument.ConvertNumbersToText

wrdDoc.Range.WholeStory
wrdApp.ActiveDocument.Content.Font.Name = "Symbol"

MyString = ChrW(61623)
MsgBox MyString
wrdDoc.Range.Find.ClearFormatting
wrdDoc.Range.Find.Replacement.ClearFormatting
With wrdDoc.Range.Find
.Text = MyString
.Replacement.Text = ";"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

wrdDoc.Range.Find.Execute Replace:=wdReplaceAll

If wrdDoc.Range.Find.Found = True Then
MsgBox "Found Unicode Bullet Point - Replacing!", vbInformation, "Value"
End If

wrdDoc.Save
wrdDoc.Close
wrdApp.Quit

Set wrdDoc = Nothing
Set wrdApp = Nothing

MsgBox "Finished Replacing Bulleted Lists", vbInformation, "Information"
End Sub


I am unsure as to whether I should be getting rid of the bulleted formatting before searching to replace the non-formated circular bullet character and even more unsure as to the actual Integer value for a circular bullet as different web sites quote different numbers.

Any advice would be much appreciated! http://www.andreavb.com/forum/emoticons/smiley.gif500) {this.width=500;this.alt='Full View';}" border=0> (http://www.andreavb.com/forum/emoticons/smiley.gif)

[fumei]

Sub ReplaceBullets()
Dim oPara As Paragraph
Dim r As Range
For Each oPara In ActiveDocument.Paragraphs()
Set r = oPara.Range
If r.ListFormat.ListType = wdListBullet Then
r.ListFormat.RemoveNumbers _
NumberType:=wdNumberParagraph
r.InsertBefore Text:=";"
End If
Set r = Nothing
Next
End Sub

will remove bullets, and replace them with a semicolon. It maintains the paragraph separations.

[Vis_Bas_App]

Fumei,

Thanks for the advice I can see where you are going with that but when I modified your code to suit my program the range variable r was being assigned the actual bullet point itself so I got a type mis match:

Sub ReplaceBullets()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim oPara As Paragraph
Dim r As Range
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open("C:\Hello14.doc")
For Each oPara In wrdDoc.Paragraphs()
Set r = oPara.Range
If r.ListFormat.ListType = wdListBullet Then
r.ListFormat.RemoveNumbers _
NumberType:=wdNumberParagraph
r.InsertBefore Text:=";"
End If
Set r = Nothing
Next
wrdDoc.Save
wrdDoc.Close
wrdApp.Quit

Set wrdDoc = Nothing
Set wrdApp = Nothing

MsgBox "Finished Sorting Bulleted Lists", vbInformation, "Information"
End Sub

Please advise.

[fumei]

Ah, are you running this FROM Excel? yeah, I guess you are. If so, change:

Dim r As Range to
Dim r As Word.Range

You should also change (to be consistent)
Dim oPara As Word.Paragraph

If you are running this from Excel, the declaration as Range will be assumed to be an EXCEL Range object - and it is a different object from a Word Range.

[MOS MASTER]

Hi and welcome to VBAX! :hi:

The code presented by Gerry is excellent as usual! :clap:

However I do wonder if it isn't more appropiate if you just put a bookmark or a tag around the total list so you can identify it as a whole.

The converting to normal text has to be done as well I presume but in that way you can reference the list in one single command.

Could you perhaps share with us how you would get the list into Excel only by Semicolons?

If the semicolon approach is sufficient for you then that's fine..if not just let me know..:whistle:

[fumei]

Joost...as usual, is rather complimentary, and, as usual, has a good point.

The question is: does the bulleted list need to be maintain as a unit?

If not, and you are only interested in the items of the list, then bookmarking the whole list is not relevant, as you still need to process each paragraph (each item) individually.

Yes, you could then add a routine to only look for bookmarked lists, and that could speed things up...however your post was regarding the removal of bullets.

BUT, you also mentioned cells in a Word table. So I am not exactly sure what is going on, as does this mean you have bulleted lists IN the cells?

[MOS MASTER]

Hi Gerry, :yes

Those where the questions I had as well when I've read this topic.
Do they have to be maintained as a whole? Would seam logic to me..

Further more the tables with the bullet part in it had me baffeld as well. Perhaps we can see a example from the OP?

But of course I could be mistaken and he can make it work just fine with only the semicolon because I haven't got the faintest idea of how he intends to do this...

Well I'm sure he's going to surprise us..:whistle:

[mdmackillop]

Hi VisBasApp,
If you select your code and click on ther VBA button, it will format it as shown
Regards
MD

[r.k.chary]

Hi,
I wanted to replace bullets points followed with text with "-" followed with same text only for specific table like table 2 or table 3 and not for entire tables in a document

Basically my word document as 4 tables with 1 or 2 tables column heading as "test details" and table contents preceeded with bullet points and I want to replace and prefix with "-"
only for that tables.
I saw your post and the above example is doing my needs but doing it for entire document not for specific table. so please help

[macropod]

Try:

Sub ReplaceTableBullets()
Dim Tbl As Table, oPara As Paragraph
For Each Tbl In ActiveDocument.Tables
For Each oPara In Tbl.Range.ListParagraphs
With oPara.Range
If .ListFormat.ListType = wdListBullet Then
.ListFormat.RemoveNumbers NumberType:=wdNumberParagraph
.InsertBefore Text:="-"
End If
End With
Next
Next
End Sub