Insert Comments

[dmorri35]

I would like to use the "insert comments" function in conjunction with a
pre-made list of comments (which I have stored in excel).

I would like to do something like this: identify each pre-made comment with
a number, and then simply push a button in reference to that number to insert
the text associated with that number. this way, I don't have to keep writing
similar comments into the document from scratch each time they come up.

Any suggestions?

 

[Doug Robbins - Word MVP]

This is untested so may need a bit of tweaking, but if you have the assign
the name ArCommentsRange to the Range in the spreadsheet that contains the
comments and modify the path in the following code as appropriate and you
insert 1 as a comment in the document where ever you want the first comment
from your Excel Spreadsheet to appear in the document, 2 where ever you want
the second to appear and so on and then if you run a macro containing the
following code, it should replace those numerals with the respective
comments from the Excel source:

'Uses early binding see:
http://www.word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm
'You need to set a reference in your project to the “Microsoft Excel Object
Libraryâ€.
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim ArComments As Variant
Dim bStartApp As Boolean
Dim acomment As Comment
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
bStartApp = True
Set xlApp = New Excel.Application
End If
On Error GoTo 0
With xlApp
Set xlbook = .Workbooks.Open("F:\Data Stores\sourceSpreadsheet.xls")
'modify path
ArComments = xlbook.Names("ArCommentsRange").RefersToRange.Value
xlbook.Close SaveChanges:=False
Set xlbook = Nothing
End With
If bStartApp Then xlApp.Quit
Set xlApp = Nothing

For Each acomment In ActiveDocument.Comments
If Isnumeric(acomment.Range.Text) then
acomment.Range.Text = ArComments(acomment.Range.Text - 1)
End if
Next acomment


--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.