Excel to Word - Inserting Chart and Comment with Excel filename inside

[reganhayward]

Hi guys

This should be a simple one...

I've got some code in Excel which formats my charts, then I have some more code to copy in my chart to Word
All fine so far,
But I want to insert a comment when I insert the chart with a hyperlink to the Excel location, so the next user knows the location.
But for the life of me I cant get it working.

Ive tried this from inside Word and it works fine

ActiveDocument.Comments.Add Range:=Selection.Range, _ Text:="review this"</pre>
But when I try that code from Excel, it gives me an error


Any ideas?


here's my current code:

Dim WDApp As Word.Application
Dim WDDoc As Word.Document


' Make sure a chart is selected
If ActiveChart Is Nothing Then
MsgBox "Please select a chart and try again.", vbExclamation, _
"No Chart Selected"
Else
' Reference existing instance of Word
Set WDApp = GetObject(, "Word.Application")
' Reference active document
Set WDDoc = WDApp.ActiveDocument

' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
Format:=xlPicture


'Paste chart at cursor position
WDApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False

'Insert comment here...
WDApp.Selection.Comments.Add Range:=Selection.Range, Text:="Comment text"




Thanks in advance

Regan