Export Images in Excel Comment to Word with VBA Questrion?

[MXL]

hi,

I was able to with the following VBA code, quickly export all comments in text format of active worksheet to the Word document.

Sub CopyCommentsToWord() 'Update 20140325 Dim xComment As Comment Dim wApp As Object On Error Resume Next Set wApp = GetObject(, "Word.Application") If Err.Number <> 0 Then Err.Clear Set wApp = CreateObject("Word.Application") End If wApp.Visible = True wApp.Documents.Add DocumentType:=0 For Each xComment In Application.ActiveSheet.Comments wApp.Selection.TypeText xComment.Parent.Address & vbTab & xComment.Text wApp.Selection.TypeParagraph Next Set wApp = Nothing End Sub Now, I want to export images also within those comment tags that I have in that excel workbook, is there something that i need to add in that existing vba code above which will enable me to extract just images from the comments within those cells? Thank you for taking the time to read this, much appreciated, look forward to hearing from you Vladmir

<tbody>
</tbody>

 

[Macropod]

This line:
wApp.Selection.TypeText xComment.Parent.Address & vbTab & xComment.Text
in your existing code is explicitly extracting only the comment text.

Try something along the lines of:

Sub CopyCommentsToWord()
Dim xComment As Comment, wApp As Object, bVis As Boolean
On Error Resume Next
Set wApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
  Err.Clear
  Set wApp = CreateObject("Word.Application")
End If
On Error GoTo 0
With wApp
  .Visible = True
  .Documents.Add DocumentType:=0
  For Each xComment In Application.ActiveSheet.Comments
    With xComment
      bVis = .Visible
      .Visible = True
      .Shape.CopyPicture
      .Visible = bVis
    End With
    With .ActiveDocument.Range
      .Collapse 0
      .Paste
      .InsertAfter vbCr
    End With
  Next
End With
Set wApp = Nothing
End Sub

PS: When posting code, please use the code tags, accessed via the # button on the posting menu.

 

[MXL]

hi Macropod,

Thank you so much, works great, I apologize for posting the VBA code in bad format, one last thing if I may, in that VBA code you wrote, is it possible to export to word the cell location from which that image had been exported from i.e ($a$1) for a cell that has a comment located in cell a1?, because from the initial VBA code i pasted, it also exports to word the cell location in which that comment is located, then the text.

Where would you have to add this line of VBA code within your VBA code in order to have the cell location exported to word along with the image?

Thank you again, look forward to hearing from you.

Regards,

Vladmir

 

[Macropod]

You could insert:
.InsertAfter xComment.Parent.Address & Chr(11)
before:
.Collapse 0

 

[MXL]

Hi Macropod,

Works fantastic, thank you again sir! much appreciated!