Export comments and corresponding text from MS Word to Excel

alimair

New Member
Joined
Jul 30, 2014
Messages
12
Hello,

I'm looking for a way to export comments from MS Word and display them in Excel alongside the corresponding highlighted text, with one text/comment pair per row. I think a macro should be able to achieve this pretty easily but I'm not very good at that sort of thing. Can anyone help?

Thanks,

Ali
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Hi, welcome to the board!

Would something like this be a good place to start?
Code:
Option Explicit

Public Sub DoYourThing()
'Requires reference to Microsoft Word v14.0 Object Library

    Dim myWord              As Word.Application
    Dim myDoc               As Word.Document
    
    Dim destSheet           As Worksheet
    Dim thisCellText        As String
    Dim thisCommentText     As String
    
    Dim rowToUse            As Integer
    Dim colToUse            As Long
    
    Set myWord = New Word.Application
    Set myDoc = myWord.Documents.Open("Your file path and file name here")
    Set destSheet = ThisWorkbook.Sheets("Sheet1")
    
    rowToUse = 2
    colToUse = 1
    
    Do
    
        'Do something to get the information from Word
        'I'll make it up for now
        thisCellText = "Cell text"
        thisCommentText = "Comment text"
        
        destSheet.Cells(rowToUse, colToUse).Value = thisCellText
        destSheet.Cells(rowToUse, colToUse).AddComment Text:=thisCommentText
        
        rowToUse = rowToUse + 1
        
    Loop Until rowToUse = 11
    
    Set myDoc = Nothing
    myWord.Quit
    
End Sub
Hope that helps.
/AJ
 

alimair

New Member
Joined
Jul 30, 2014
Messages
12
Hi AJ,

Thanks very much for your quick reply. This looks like the sort of thing, except that I don't want to look for specific comments and text, I want to export all comments and the text associated with each. Only some sentences of the text have been commented on, so I don't want to include anything that has not got a comment, and the comments are all different so looking for specific text within the comments wouldn't work. Does that help to clarify, or have I misunderstood?

Thanks,

Ali
 

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Hi Ali

Perhaps we could see a sample of some of the text, and you could show how to determine where to being and where to end copying the text?

Thanks
/AJ
 

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Actually, try this...
Code:
Option Explicit


Public Sub FindWordComments()
'Requires reference to Microsoft Word v14.0 Object Library


    Dim myWord              As Word.Application
    Dim myDoc               As Word.Document
    Dim thisComment         As Word.Comment
    
    Dim fDialog             As Office.FileDialog
    Dim varFile             As Variant
    
    Dim destSheet           As Worksheet
    Dim rowToUse            As Integer
    Dim colToUse            As Long
    
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    Set destSheet = ThisWorkbook.Sheets("Sheet1")
    colToUse = 1
    
    With fDialog
        .AllowMultiSelect = True
        .Title = "Import Files"
        .Filters.Clear
        .Filters.Add "Word Documents", "*.docx"
        .Filters.Add "Word Macro Documents", "*.docm"
        .Filters.Add "All Files", "*.*"
    End With
    
    If fDialog.Show Then
    
        For Each varFile In fDialog.SelectedItems
    
            rowToUse = 2
    
            Set myWord = New Word.Application
            Set myDoc = myWord.Documents.Open(varFile)
    
            For Each thisComment In myDoc.Comments
            
                With thisComment
                    destSheet.Cells(rowToUse, colToUse).Value = .Scope.Text
                    destSheet.Cells(rowToUse, colToUse).AddComment Text:=.Range.Text
                End With
                
                rowToUse = rowToUse + 1
                
            Next thisComment
            
            destSheet.Cells(1, colToUse).Value = "Comments from " & myDoc.Name
            
            Set myDoc = Nothing
            myWord.Quit
            
            colToUse = colToUse + 1
            
        Next varFile
    
    End If


End Sub
/AJ
 

alimair

New Member
Joined
Jul 30, 2014
Messages
12
Hi AJ,

Just tried to paste a screenshot but it came out as a huge block of text - working on a different way now...

Ali
 
Last edited:

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Ooops. Epic fail with the paste ;).

No matter. Try the code above and see if it works.

/AJ
 

alimair

New Member
Joined
Jul 30, 2014
Messages
12
Thanks again!! Unfortunately I have some (pretty stupid) questions before I can try running this one.

1) 'Requires reference to Microsoft Word v14.0 Object Library - what do I need to do here?

2) I can't see where to paste the file name and path in this one

Thanks for your patience! There's a sample of the material here, if it makes any difference: https://dl.dropboxusercontent.com/u/34061108/Untitled.png

Thanks

Ali
 

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Hi Ali

Just fyi I haven't looked at the link. Our office firewall is scared of downloads.

Anyway.

In the Visual Basic editor you need to go to the Tools Menu, and click References.
Scroll down until you find Microsoft Word 14.0 Object Library and check the box next to it.
This is so that Excel knows where to find all the information about the Word.Application object, otherwise it'll get upset when you try and use Word.Application or Word.Document, etc.

This code now, when run, will present you with a dialog box to select the Word file, or files, to work through.
I figured this was a little more user-friendly than hard coding in the file path.

Any more questions, do shout.


/AJ
 

alimair

New Member
Joined
Jul 30, 2014
Messages
12
Ahh okay - I think maybe the problem is that I only have 12.0 Object Library available, which was already selected. Can I change the code so it works for 12.0?

Thanks,

Ali
 

Watch MrExcel Video

Forum statistics

Threads
1,095,541
Messages
5,445,083
Members
405,315
Latest member
ao5835403

This Week's Hot Topics

Top