Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Export comments and corresponding text from MS Word to Excel

  1. #1
    New Member
    Join Date
    Jul 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Export comments and corresponding text from MS Word to Excel

    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

  2. #2
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export comments and corresponding text from MS Word to Excel

    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
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  3. #3
    New Member
    Join Date
    Jul 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export comments and corresponding text from MS Word to Excel

    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

  4. #4
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export comments and corresponding text from MS Word to Excel

    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
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  5. #5
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export comments and corresponding text from MS Word to Excel

    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
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  6. #6
    New Member
    Join Date
    Jul 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export comments and corresponding text from MS Word to Excel

    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 by alimair; Jul 30th, 2014 at 10:43 AM.

  7. #7
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export comments and corresponding text from MS Word to Excel

    Ooops. Epic fail with the paste .

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

    /AJ
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  8. #8
    New Member
    Join Date
    Jul 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export comments and corresponding text from MS Word to Excel

    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/...8/Untitled.png

    Thanks

    Ali

  9. #9
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export comments and corresponding text from MS Word to Excel

    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
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  10. #10
    New Member
    Join Date
    Jul 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Export comments and corresponding text from MS Word to Excel

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •