Copying comments from Word to Excel
Thank you! I'll try it out later this afternoon.
"Billy Liddel" wrote:
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
"Billy Liddel" wrote:
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
"Simone" wrote:
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!
|