Query on Word VBA - Comments.Range.Text extracting comments from a document and preserving formatting

[oitbc]

Hi All,

I've got a macro I have updated, based on some work from the past. It now sends the comments to Excel. The thing I noticed is that in using the Comments.Range.Text to get a copy of the comment, any formatting that was applied in the Comment is removed. This does not happen if I write the comment to a word table, only when I do it to a Excel cell.

For example:

If the comment says

"This isn't great, we need to repeat the newline and then make sure we display some numbered lists in it.

There is the newline above here

1. Item 1
2. Item 2"

it gets returned as a stream of

"This isn't great, we need to repeat the newline and then some numbered lists in it.There is the newlineItem 1Item 2"

when called by

Celltofill = oDoc.Comments(n).Range.Text

and n is just a counter for a for loop

Any idea how to preserve the formatting?

 

[oitbc]

Sorry, I should say that when I use the following code

Dim oTable As Table
For n = 1 To nCount
With oTable.Rows(n + 1)
'The text of the comment
.Cells(7).Range.Text = oDoc.Comments(n).Range.Text

in Word with a table then it does still remove the numbers, but does keep the newlines, so it looks like this in Word

"This isn't great, we need to repeat the newline and then make sure we display some numbered lists in it.

There is the newline above here

Item 1
Item 2"

 

[oitbc]

Solved it myself sorry

Replace(oDoc.Comments(n).Range.Text, vbCr, Chr(10))

 

[Macropod]

See: Export Comments with referred text and line numbers from Word to Excel
The code there outputs tabs and paragraph breaks as <TAB> and <P>, respectively. If all you want to do is to retain the paragraph breaks, replace "<TAB>" in the code with " " and, before:
.Columns("A:D").AutoFit
insert:
.UsedRange.Replace What:="<P>", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder:=xlByRows