Export comments and corresponding text from MS Word to Excel

[alimair]

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

 

[adam087]

Hi, welcome to the board!

Would something like this be a good place to start?

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]

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]

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]

Actually, try this...

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]

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

 

[adam087]

Ooops. Epic fail with the paste .

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

/AJ

 

[alimair]

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]

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]

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