Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

Thread: Split Words in the comment text - VBA Macro

  1. #1

    Split Words in the comment text - VBA Macro

    Hello All,
    I am trying to write a macro which will split the comment. My supervisor wants to prioritize the comments. The comment text reads as below
    Low : Comment 1
    Medium : Comment 2
    High: Comment 3
    The out put should be displayed in an excel with the following headings
    I was able to write a macro to export comments from words to an excel file. However, I am struggling to add this code snippet to split the comment text
    Comment ID Page Section/Paragraph Name Comment Scope Comment text Priority Reviewer Comment Date
    1 1 1.1heading1 example heading Comment 1 Low BlueDolphin 1/1/1
    2 2 1.2heading example2 Comment 2 Medium BlueDolphin 1/1/1
    3 3 1.3heading 3example3 Comment 3 High BlueDolphin 1/1/1
    Anyhelp is much appreciated
    Thanks
    BlueDolphin

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,247
    Location
    Since you haven't posted your code or any description of the comment content, we can only guess how you're going about this. Try something along the lines of the following:
    Sub ExportComments() 
        Dim StrCmt As String, StrTmp As String, i As Long, j As Long, xlApp As Object, xlWkBk As Object 
        StrCmt = "Page,Paragraph,Author,Date & Time,Scope,Prority,Comment" 
        StrCmt = Replace(StrCmt, ",", vbTab) 
        With ActiveDocument 
             ' Process the Comments
            For i = 1 To .Comments.Count 
                With .Comments(i) 
                    StrCmt = StrCmt & vbCr & .Reference.Information(wdActiveEndAdjustedPageNumber) & vbTab & .Range.ListFormat.ListString & vbTab & .Author & vbTab & .Date & vbTab & .Scope 
                    StrCmt = StrCmt & vbTab & Trim(Split(.Range.Text, ":")(0)) & vbTab & Trim(Split(.Range.Text, ":")(1)) 
                End With 
            Next 
        End With 
         ' Test whether Excel is already running.
        On Error Resume Next 
        Set xlApp = GetObject(, "Excel.Application") 
         'Start Excel if it isn't running
        If xlApp Is Nothing Then 
            Set xlApp = CreateObject("Excel.Application") 
            If xlApp Is Nothing Then 
                MsgBox "Can't start Excel.", vbExclamation 
                Exit Sub 
            End If 
        End If 
        On Error GoTo 0 
        With xlApp 
            Set xlWkBk = .Workbooks.Add 
             ' Update the workbook.
            With xlWkBk.Worksheets(1) 
                For i = 0 To UBound(Split(StrCmt, vbCr)) 
                    StrTmp = Split(StrCmt, vbCr)(i) 
                    For j = 0 To UBound(Split(StrTmp, vbTab)) 
                        .Cells(i + 1, j + 1).Value = Split(StrTmp, vbTab)(j) 
                    Next 
                Next 
                .Columns("A:J").AutoFit 
            End With 
             ' Tell the user we're done.
            MsgBox "Workbook updates finished.", vbOKOnly 
             ' Switch to the Excel workbook
            .Visible = True 
        End With 
         ' Release object memory
        Set xlWkBk = Nothing: Set xlApp = Nothing 
    End Sub 
    
    
    Formatting tags added by mark007
    I've omitted the Comment ID as MS has deprecated support for it.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    Here is my code;



    Sub exportCommentsToExcel() 
        Dim xlApp As Object 
        Dim xlWB As Object 
        Dim n As Integer, AddRow As Integer 
        Dim objPara As Paragraph 
        Dim objComment As Comment 
        Dim strSection As String 
        Dim strTemp 
        Dim myRange As Range 
         
         
        Set xlApp = CreateObject("Excel.Application") 
        xlApp.Visible = True 
        Set xlWB = xlApp.Workbooks.Add 
        With xlWB.Worksheets(1) 
            .Name = "Comments" 
            AddRow = 1 
            .Cells(AddRow, 1).Formula = "Comment ID" 
            .Cells(AddRow, 2).Formula = "Page" 
            .Cells(AddRow, 3).Formula = "Paragraph Name" 
            .Cells(AddRow, 4).Formula = "Selected text" 
            .Cells(AddRow, 5).Formula = "Comment text" 
            .Cells(AddRow, 6).Formula = "Reviewer" 
            .Cells(AddRow, 7).Formula = "Comment Date" 
            strSection = "preamble" 
            strTemp = "preamble" 
            If ActiveDocument.Comments.Count = 0 Then 
                MsgBox ("There are no comments made in this document.") 
                Exit Sub 
            End If 
            For n = 1 To ActiveDocument.Comments.Count 
                Set myRange = ActiveDocument.Comments(n).Scope 
                strSection = ParentLevel(myRange.Paragraphs(1)) 
                .Cells(n + AddRow, 1).Formula = ActiveDocument.Comments(n).Index 
                .Cells(n + AddRow, 2).Formula = ActiveDocument.Comments(n).Reference.Information(wdActiveEndAdjustedPageNumber) 
                .Cells(n + AddRow, 3).Value = strSection 
                .Cells(n + AddRow, 4).Formula = ActiveDocument.Comments(n).Scope 
                .Cells(n + AddRow, 5).Formula = ActiveDocument.Comments(n).Range 
                .Cells(n + AddRow, 6).Formula = ActiveDocument.Comments(n).Author 
                .Cells(n + AddRow, 7).Formula = Format(ActiveDocument.Comments(n).Date, "MM/dd/yyyy") 
                .Cells(n + AddRow, 8).Formula = ActiveDocument.Comments(n).Range.ListFormat.ListString 
            Next n 
             
        End With 
        Set xlWB = Nothing 
        Set xlApp = Nothing 
    End Sub 
    
    
    Formatting tags added by mark007


    Function ParentLevel(Para As Word.Paragraph) As String 
        Dim ParaAbove As Word.Paragraph 
        Set ParaAbove = Para 
        sStyle = Para.Range.ParagraphStyle 
        sStyle = Left(sStyle, 4) 
        If sStyle = "Head" Then 
            GoTo Skip 
        End If 
        Do While ParaAbove.OutlineLevel = Para.OutlineLevel 
            Set ParaAbove = ParaAbove.Previous 
        Loop 
    Skip: 
        strTitle = ParaAbove.Range.Text 
        strTitle = Left(strTitle, Len(strTitle) - 1) 
        ParentLevel = ParaAbove.Range.ListFormat.ListString & " " & strTitle 
    End Function 
    
    
    Formatting tags added by mark007
    Currently it gives this output

    Comment ID Page Paragraph Name Selected Text Comment text Reviewer Comment Date
    1 1 1.1heading1 example heading Comment 1 BlueDolphin 1/1/1
    2 2 1.2heading example2 Comment 2 BlueDolphin 1/1/1
    3 3 1.3heading 3example3 Comment 3 BlueDolphin 1/1/1
    Last edited by Paul_Hossler; 12-08-2017 at 01:55 PM.

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,247
    Location
    Did you try the code I posted? Did you look at how I implemented the comment splitting? Your latest table doesn't even indicate the provision for or presence of your priorities.

    PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    I have tried your code, It gives me an error "Runtime error - Subscription out of range. I apologize I didnt know that I had to use code tags.

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,247
    Location
    Quote Originally Posted by bluedolphin View Post
    I have tried your code, It gives me an error "Runtime error - Subscription out of range.
    That suggests one or more comments don't have the
    Low: comment text
    Medium: comment text
    High: comment text
    in them that your first post indicated.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  7. #7
    Quote Originally Posted by bluedolphin View Post
    I apologize I didnt know that I had to use code tags.
    I added them to your post for you

    As (the other) Paul says, it makes your macro(s) easier to see and does some formatting

    You might take a look at the forum's FAQs (checkout my sig)
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    Thanks Paul. I did add them comments in that style. The comments I wrote in the document are something like Low:"comment text", etc.. I am trying to do more research on your code to fix the issue. Any help from you is much appreciated.

  9. #9

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,247
    Location
    The code I posted works correctly for the comment data you described. If you're not getting the same outcome, either you've modified the code or one or more of your comments don't conform to your description.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  11. #11
    I haven't changed your code and my comments this time actually say "comment", The error says " Run-time error '9': Subscript out of range"

  12. #12
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,247
    Location
    In which case one or more of your comments don't conform to your description.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  13. #13
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,247
    Location
    Cross-posted at: https://answers.microsoft.com/en-us/...1-bc479404c314
    Kindly read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Cheers
    Paul Edstein
    [MS MVP - Word]

  14. #14
    I apologize for posting multiple places. i did not know about cross posting. I am a newbie to VB Macro and infact this is the first time i have posted in a forum. I will take down other posts. Thanks for letting me know

  15. #15
    First of all thanks a lot for your help.
    I am not sure why the code isn't working at work computer. When I tried at home computer it worked. Apparently, the code is not supportive to 2013 word. I am looking into the code however, any help from you is very much appreciated.

  16. #16
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,247
    Location
    The Word version should be of no consequence. What error message, if any, are you getting?
    Cheers
    Paul Edstein
    [MS MVP - Word]

  17. #17
    The error says " Run-time error '9': Subscript out of range". The excel workbook doesn't have any details in it when created. I debugged the code to see where it is failing. Its failing here

    For i = 1 To .Comments.Count 
        With .Comments(i) 
            StrCmt = StrCmt & vbCr & .Reference.Information(wdActiveEndAdjustedPageNumber) & vbTab & .Range.ListFormat.ListString & vbTab & .Author & vbTab & .Date & vbTab & .Scope 
            StrCmt = StrCmt & vbTab & Trim(Split(.Range.Text, ":")(0)) & vbTab & Trim(Split(.Range.Text, ":")(1)) 
        End With 
    Next 
    
    
    Formatting tags added by mark007

  18. #18
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,247
    Location
    As I have said repeatedly, that error means:
    one or more of your comments don't conform to your description
    All you're doing is wasting my time (and yours) if you don't fix your comments to make them conform to your own specifications. And I'm not about to waste any more time on that.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  19. #19
    I have done exactly like you said. The code doesn't work at work computer.
    Thanks for your valuable time.I apologize that i have upset you., I appreciate your help. I will continue to work on it.

    Thanks
    Jo. D

  20. #20
    Maybe you can post a sanitized version of your document here

    The document's text most likely isn't important, but include the macro you're using and the comment(s) that show the error
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

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