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]

Hmmm. What version of Office are you using?

You need Word, Excel and Office object libraries selected.

I can't see why it wouldn't work with v12 actually, none of the objects are particularly new. Does it give you an error when you try and use it?

/AJ

 

[alimair]

Yes - it says "Compile error - User-defined type not defined" when I run it. It's the 2007 version I'm using.

Thanks,

Ali

 

[adam087]

And which line of code is that highlighting when you click through the error?

/AJ

 

[alimair]

Oh hold on... I've managed to run it now, sorry, I had the MS Office Object Library selected and the Excel one, but not the Word one - so it does work with 12.0! It's great, but there's one more thing... sorry to be a pain... at the moment the comments from Word also display as comments in Excel - is it possible to have them displayed in the cell next to the corresponding text instead (i.e. column B)?

Thanks so much!

Ali

 

[adam087]

Ah that is excellent news. And yes, sure. Updated 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
    
    ActiveSheet
    
    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 + 1).Value = 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 + 2
            
        Next varFile
    
    End If

End Sub

/AJ

 

[alimair]

Oh no! Now I'm getting an error message saying "Compile error: Invalid use of property" - the line that says ActiveSheet is selected when the error message comes up...

 

[adam087]

Crikey. Wonder how that got in there. You can delete that line completely!

/AJ

 

[alimair]

Thanks! Okay now I have an error message with this line:

destSheet.Cells(rowToUse, colToUse + 1).Value = Range.Text

"Range" is highlighted and it sas "Compile error: Argument not optional"

 

[adam087]

Put a dot in front of Range

destSheet.Cells(rowToUse, colToUse + 1).Value = .Range.Text

AJ

 

[alimair]

Perfect! Thanks so much!

Ali