none
Help needed in vbs file involving "comments" functionality of MS Word 2002 RRS feed

  • Question

  • Wrote a VBS to get comments from all the word documents present in a folder and write them into an excel sheet with separate sheets for each document. But the page number and line number seems to be not working. Tried many different ways, googled a thousand times but to no avail...frustrated now...please if someone could point out whats the problem here...

    Option Explicit
    On Error Resume Next
    Dim appObj, exlObj, wBkObj, wShtObj, wrdObj
    Dim p, q, r
    Dim srcFolder, strFolder, strFile, wordDoc, fileName, docPath, sheetName
    Dim myStr
    Dim FSO, FLD, FIL
    Dim cmntCntr, totCmnts, cmntObj
    Set wrdObj = createObject("Word.Application")
    Set exlObj = CreateObject("Excel.Application")
    Set srcFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder...", 0)
    If (Not srcFolder Is Nothing) Then strFolder = srcFolder.Items.Item.Path
    Set srcFolder = Nothing
    MsgBox " Folder --> " & strFolder
    fileName = strFolder & "\" & "review_comments_details.xls"
    MsgBox "XLFileName -->" & fileName
    q = 0
    docPath = strFolder & "\*.doc"
    MsgBox "Word Path -->" & docPath
    'Create the filesystem object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    'Get a reference to the folder you want to search
    set FLD = FSO.GetFolder(strFolder)
    'loop through the folder and get the file names
    For Each Fil In FLD.Files
         MsgBox Fil.Name
         MsgBox Fil.Type
         if Fil.Type = "Microsoft Word Document" then
            Set wordDoc = wrdObj.Documents.Open(strFolder & "\" & Fil.Name)
            wrdObj.Application.visible = false
            wrdObj.Application.DisplayAlerts = False
            With wordDoc
               MsgBox "opened word document -->" & wordDoc
               sheetName = Mid(Fil.Name, 1, Len(Fil.Name) - 4)
               MsgBox "Sheet Name -->" & sheetName
               p = 0
               r = 1
               totCmnts = wordDoc.Comments.count
               If totCmnts > 0 Then
                  q = q + 1
                  MsgBox "Document No -->" & q
                  'Start Excel
                  MsgBox "No of comments -->" & totCmnts
                  If q = 1 Then
                     MsgBox "Opening Excel Workbook"
                     Set wBkObj = exlObj.workbooks.Add()
                  End If
                  MsgBox "Adding Workbook Sheet --> " & q
                 Set wShtObj = wBkObj.Worksheets(q)
                 wShtObj.Name = sheetName
                  wShtObj.Cells(r, 1) = "Serial No"
                 wShtObj.Cells(r, 2) = "Page No"
                 wShtObj.Cells(r, 3) = "Line No"
                 wShtObj.Cells(r, 4) = "Author"
                 wShtObj.Cells(r, 5) = "Comment Text"
                 wShtObj.Cells(r, 6) = "Status"
                  For Each cmntObj In wordDoc.Comments
                     r = r + 1
                     p = p + 1
                     wShtObj.Cells(r, 1) = p
    'the trouble code starts
                     wShtObj.Cells(r, 2) = cmntObj.Scope.Paragraphs(1).Range.Information(wdActiveEndPageNumber)
                     wShtObj.Cells(r, 3) = cmntObj.Scope.Paragraphs(1).Range.Information(wdFirstCharacterLineNumber)
    'the trouble code ends
                     wShtObj.Cells(r, 4) = cmntObj.Author
                     wShtObj.Cells(r, 5) = cmntObj.Range.Text
                     MsgBox "Comment1 --> " & p
    '                cmntCntr = cmntObj.Scope.Information(wdActiveEndPageNumber)
    '                cmntCntr = cmntObj.Scope.get_Information(wdActiveEndPageNumber)
                     cmntCntr = cmntObj.Scope.Paragraphs(1).Range.Information(wdActiveEndPageNumber)
                     MsgBox "Comment2 --> " & cmntCntr
                  Next
               End If
            end with
            wordDoc.Close
         end if
    Next
    wrdObj.quit
    If Not exlObj Is Nothing Then
         MsgBox "Saving Excel Workbook"
         wBkObj.SaveAs fileName
    End If
    exlObj.quit
    MsgBox Err.Description, vbExclamation
    Monday, September 17, 2012 4:56 AM

Answers

  • Tried that too...

    Is it possible that the enumerators wdPrintView, wdActiveEndPageNumber, wdFirstCharacterLineNumber etc are not available outside the Word application. I mean, these things give values when called from a macro invoked from inside the Word application but they throws error as "variable is undefined" when used inside a .vbs file. If that's the case then knowing page number or line number is a no no!! from .vbs ... My idea of making it a standalone application will go down the drain then.. :'-(

    Monday, September 17, 2012 12:24 PM
  • Hi bijunator

    <thunk - sound of head hitting hand>

    Yes! Sorry that didn't occur to me - but I think you've found the problem. Good detective work. Shows how long it's been since I've worked with late-binding...

    Go to Word's VBA Editor, press F2 to open the Object Browser. enter the name of the Enum member (wdPrintView, etc) in the search box, click the Binoculars. You should get an entry for the entire Enum in the window just below. Click on that and the list should appear in the gray area below that. Click on the member (wdPrintView, etc.) and in the very bottom section you should see the numeric equivalent for the Enum (a "Long" value). Use that in your code.


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by bijunator Tuesday, September 18, 2012 4:10 AM
    Monday, September 17, 2012 2:12 PM
    Moderator

All replies

  • Hi bijunator

    1. Which version of Word?

    2. Please explain how "it's not working"

    3. From a quick scan of your code, I think you'll need to set the Word Application.Visible = True. In any case, I recommend you test it in that mode.

    Word was designed as an end-user tool. Some of it's layout functionality only works correctly if the application is visible. Since your difficulties revolve exactly around properties that depend on the layout, that's the first suspect.

    Please also note that a Word document might lay out differently on different machines, with different printer drivers. This can also influence on which line/page text will appear.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, September 17, 2012 6:13 AM
    Moderator
  • 1. MS Word 2002

    2. The code -->

    'the trouble code starts
       wShtObj.Cells(r, 2) = cmntObj.Scope.Paragraphs(1).Range.Information(wdActiveEndPageNumber)
       wShtObj.Cells(r, 3) = cmntObj.Scope.Paragraphs(1).Range.Information(wdFirstCharacterLineNumber)
    'the trouble code ends

    The above code is supposed to show the line numbers and page numbers of the comments its iterating. Its not showing the same 

    The same code worked when I tried the whole code as a macro but when I changed the code to a standalone .VBS file, the code is not showing page number and line number of the comments its picking up.

    3. Tried with Application.Visible = True too but doesn't work. The reason I made it false is that I didn't wanted to display the word files I was working on.

    The code is supposed to do this --

    1. Open up a browse folder window.

    2. Loop through all the word documents present in the folder chosed above.

    3. Get all the comments present in each document and log them into an excel sheet. One workbook sheet per each word document containing comments.

    4. The excel sheet would contain columns -- serial number, page number, line number, author, comment text

    Also tried several different ways to display line number/pagenumber but none of them seems to work --

    cmntObj.Scope.Information(wdActiveEndPageNumber)
    cmntObj.Scope.get_Information(wdActiveEndPageNumber)
    cmntObj.Scope.Paragraphs(1).Range.Information(wdActiveEndPageNumber)

    Am I missing something here!!!

    Monday, September 17, 2012 7:10 AM
  • Hi bijunator

    Thank you for the additional information and I apologize for not having seen the version number in the subject. So you're not getting any numbers at all, is that correct?

    What happens if you try using the Reference and the Range properties instead of Scope? Does that make any difference? And for the moment I'd leave the Word application window visible.

    Note that I'm assuming "the obvious": there really are comments in the document you're testing?

    I can't think of any reason why a script running outside Word would behave differently than a macro within the application, except for the fact that you can be pretty sure the application window is laying things out.

    Ah, one other thought: In which view is the document opening? If it's not "Page Layout" that could be the problem.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, September 17, 2012 7:27 AM
    Moderator
  • Hi Cindy,

    Thank you for your time. I have tried all these below ways to display them but failed.

    cmntObj.Scope.Information(wdActiveEndPageNumber)
    cmntObj.Scope.get_Information(wdActiveEndPageNumber)
    cmntObj.Scope.Paragraphs(1).Range.Information(wdActiveEndPageNumber)
    cmntObj.Reference.Information(wdActiveEndPageNumber)
    wordDoc.Comments(p).Reference.Information(wdActiveEndPageNumber)

    Also, I have added the below highlighted code to the existing code so as to have the PrintView layout but still it doesn't work...

           wrdObj.Application.DisplayAlerts = False
        With wordDoc
                wdDoc.ActiveWindow.ActivePane.View.Type = wdPrintView '--new code line added
           MsgBox "opened word document -->" & wordDoc
               sheetName = Mid(Fil.Name, 1, Len(Fil.Name) - 4)

    But it works like charm when I do it as a macro...WHY!!! mommieee.. :'-(

    Monday, September 17, 2012 9:05 AM
  • Hi bijunator

    I can suggest only one more thing to try: use the Select method on each comment (with the Word application visible) then try Selection.Information

    It's not pretty, I know, but let's see if that can at least get Word's attention...


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, September 17, 2012 9:22 AM
    Moderator
  • Tried that too...

    Is it possible that the enumerators wdPrintView, wdActiveEndPageNumber, wdFirstCharacterLineNumber etc are not available outside the Word application. I mean, these things give values when called from a macro invoked from inside the Word application but they throws error as "variable is undefined" when used inside a .vbs file. If that's the case then knowing page number or line number is a no no!! from .vbs ... My idea of making it a standalone application will go down the drain then.. :'-(

    Monday, September 17, 2012 12:24 PM
  • Hi bijunator

    <thunk - sound of head hitting hand>

    Yes! Sorry that didn't occur to me - but I think you've found the problem. Good detective work. Shows how long it's been since I've worked with late-binding...

    Go to Word's VBA Editor, press F2 to open the Object Browser. enter the name of the Enum member (wdPrintView, etc) in the search box, click the Binoculars. You should get an entry for the entire Enum in the window just below. Click on that and the list should appear in the gray area below that. Click on the member (wdPrintView, etc.) and in the very bottom section you should see the numeric equivalent for the Enum (a "Long" value). Use that in your code.


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by bijunator Tuesday, September 18, 2012 4:10 AM
    Monday, September 17, 2012 2:12 PM
    Moderator
  • You are an angel. :-D

    This is my first experience with word programming. I was trying to show-off amongst my colleagues and I was almost on the verge of giving up. I am back in the game now...thanks to you...

    Tuesday, September 18, 2012 4:13 AM
  • Well, I'm loathe to take much credit as you're the one who tracked down what the real problem was :-) But I'm glad things are up-and-running - and pretty impressive for someone programming Word for the first time!


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, September 18, 2012 7:25 AM
    Moderator