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

oitbc

New Member
Joined
Mar 11, 2019
Messages
12
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

VBA Code:
Celltofill = oDoc.Comments(n).Range.Text
and n is just a counter for a for loop

Any idea how to preserve the formatting?
 
Last edited:

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

oitbc

New Member
Joined
Mar 11, 2019
Messages
12
Sorry, I should say that when I use the following code

VBA 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

New Member
Joined
Mar 11, 2019
Messages
12
Solved it myself sorry

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

BlandMosquito

Spammer
Joined
Jul 24, 2020
Messages
2
Office Version
365
Platform
Windows

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,588
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top