Skip to main content

How to export comments from Excel to Word?

For Excel users, inserting comments into the cells is a common task, sometimes, you need to copy and paste these comments to a Word document for some purpose. Of course, you can copy and paste the comment to the Word one by one, but this is a tedious and time-consuming job. Is there any way to export all the comments from Excel to Word at once?

Export comments from current worksheet to Word with VBA code

arrow blue right bubbleExport comments from current worksheet to Word with VBA code

Unfortunately, Excel doesn’t provide the feature to copy and paste all the comments to Word at once. However, with the following VBA code, you can quickly export all comments of active worksheet to the Word document.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Export comments from current worksheet to Word

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

3. Then press F5 key to run this code, and all of the comments in the active worksheet have been exported to a new Word document with itscell address. See screenshot:

doc-export-comments1

Related articles:

How to quickly insert a comment to multiple cells in Excel?

How to change the shape of comment box in Excel?

How to change all comment formats in cells in Excel?

How to change all comments’ author name in Excel?

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

kte tab 201905

60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 
 
Comments (17)
No ratings yet. Be the first to rate!
Aba
Aba
This comment was minimized by the moderator on the site
This is excellent! however i am struggling to pin the comments to where it belong in the  worksheet. is there any way to be able to label the rows or column or cell in which the comments is extracted?
Reply
0
0
ibson
ibson
This comment was minimized by the moderator on the site
Able To Extract starts with the initial measurable data set and generates derived values ​.Users can insert and customize their numbers to professionally index business.
Philip
patchhere.com
Reply
Report
0
0
Jason
Jason
This comment was minimized by the moderator on the site
Hey I figured out how to fix this. Line 13 needs to read "For Each xComment In Application.ActiveSheet.CommentsThreaded". (The output isn't exactly like the screenshot, you would have to add xComment.Author or perhaps some other properties in the For loop - but changing from Comments to CommentsThreaded gets this working again.)
Reply
0
0
Sierra
Sierra
This comment was minimized by the moderator on the site
I copied and pasted exactly but it only generated a blank word document. Any ideas?


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
Reply
0
0
Dario
Dario     Sierra
This comment was minimized by the moderator on the site
same issue. did you manage to solve?
Reply
0
0
Larina
Larina     Dario
This comment was minimized by the moderator on the site
Same...any thoughts?
Reply
0
0
Sam
Sam
This comment was minimized by the moderator on the site
This is super helpful! Thanks!
Is there a way to pull in the row or column header rather than the cell position (B4)?
Reply
0
0
Evaristo
Evaristo     Sam
This comment was minimized by the moderator on the site
I was able to get this result by changing the "xComment.Parent.Address" to "xComment.Parent.Value" in row 14.
Reply
0
0
Jay Ray
Jay Ray
This comment was minimized by the moderator on the site
omg! it was really helpful!
Reply
0
0
Darrell
Darrell
This comment was minimized by the moderator on the site
This is great and helps! Is there a way to get actual text within the cell to replace the cell number or to be included? I have headers and balloon instructions in the headers. I am trying to create a word procedure on filling out the rows with headers of the data (Ex.; "Name") and then the comment box tells the user the format.

Currently Shows with above macro:

A1 Username:


- Enter Name of Borrower as seen on Borrower organizational documents or other name supporting documents.


Looking for it so show the text of cell A1 which is Company Name.


Any help greatly appreciated!
Reply
0
0
Bartolomeu
Bartolomeu
This comment was minimized by the moderator on the site
Can it be done to extract from filtred rows after applying filter ? Thank you.
Reply
0
0
peod
peod
This comment was minimized by the moderator on the site
it really helped. tq!
Reply
0
0
david
david
This comment was minimized by the moderator on the site
does anyone know if I can do this but only copying comments from a specific row? any help will be much appreciated ive been trying to work on this for ages.
Reply
0
0
randi
randi
This comment was minimized by the moderator on the site
get the message "Compile error: Invalid outside procedure". Now what?
Reply
0
0
Kate
Kate     randi
This comment was minimized by the moderator on the site
I had the same message - did you manage to resolve this Randi?
Reply
0
0
Gillian
Gillian
This comment was minimized by the moderator on the site
This worked perfectly and was exactly what I needed! Thank you so much.
Reply
0
0
vladmir
vladmir
This comment was minimized by the moderator on the site
hi, Excellent article, I was curious to know, if I have images in those comments, will that VBA code above also quickly export all images in the comments that images of an active worksheet to the Word document? Thank you for taking the time to read this, much appreciated. Regards, Vladmir
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations