Copying comments from Word to Excel

[Simone]

I have a large table in Word which has comments on some of the lines. I'd
like to copy this into Excel but it doesn't seem to work.

I've tried just a straight copy & paste and it puts the comments below the
table but not associated with lines. Then I tried to save as a Web page and
open in Excel. That didn't work either.

Any suggestions would be appreciated!

 

[Billy Liddel]

Simone

I tried this with a simple table 4 rows, and just pasted the data into
Excel. The comments were entered into A6 to A8. I knew what cells contained
the comments so I selected them and created a range name called CommentCells.

The following macro placed the comments in the range CommentCells and added
the text for the appropriate cell in the range A6:A8. This is rng2 in the
macro.

If you have not used a macro before then key (ALT + F8, Insert, Macro) and
copy the code below.

Sub InsertComments()
' Create Range name for cells where comments are to go
' before running this macro

Dim rng1 As Range
Dim rng2 As Range
Dim lnRows As Long
Dim c As Range
Dim d As Range
Dim counter As Long
Dim sCmt As String

Set rng1 = Range("CommentsCells")
' change this cell address
lnRows = Range("A6").CurrentRegion.Rows.Count

If rng1.Count <> lnRows Then
MsgBox "The source & destination rows are not the same, Please check"
Exit Sub
End If

Set rng2 = Range(Cells(6, 1), Cells(lnRows + 5, 1))
rng2.Select
For Each c In rng1
counter = counter + 1
Range(c.Address).AddComment
sCmt = rng2(counter)
MsgBox sCmt
Range(c.Address).Comment.Text Text:=sCmt

Next c

End Sub

Return to Excel and run the code (ALT + F8), select macro and click Run.

Regards

Peter Atherton

 

[Billy Liddel]

Oops I forgot to remove the secong MessageBox from the code, it was just used
in testing.

' MsgBox sCmt
Range(c.Address).Comment.Text Text:=sCmt

Next c

Delete this line MsgBox sCmt or put an apostrophe at the start of the line
as shown


Peter

 

[Simone]

Thank you! I'll try it out later this afternoon.

Oops I forgot to remove the secong MessageBox from the code, it was just used
in testing.

' MsgBox sCmt
Range(c.Address).Comment.Text Text:=sCmt

Next c

Delete this line MsgBox sCmt or put an apostrophe at the start of the line
as shown


Peter