Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    McLean, VA
    Posts
    125
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Macro to Export Comments and Tracked Changes (2003/SP1)

    I need a macro that will export the comments/tracked changes of a Word doc to an Excel spreadsheet. I also need to export some reference information, for example what section/page number/etc the comment was found in. Each comment and associated reference information needs to be in its own row of the spreadsheet, preferably having the comment/tracked change and the reference info in their own cells of the row.

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Macro to Export Comments and Tracked Changes (2003/SP1)

    How much help do you need to write this Macro?
    Can you create a simple Macro?
    Can you loop through the comments in Word getting the text and finding the Section and Page Number?
    Can you create an Excel Document?
    Can you put data into specific cells in an Excel document?

    If you can do all of these, then it'll be easy. Otherwise let us know what help you need and I'm sure someone will help you out.

    StuartR

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    McLean, VA
    Posts
    125
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Macro to Export Comments and Tracked Changes (2003/SP1)

    I have only created simple macros, edited recorded macros, and pasted in the code of others, so I need help with pretty much all of the items you listed.

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Macro to Export Comments and Tracked Changes (2003/SP1)

    Here is a very basic Macro to get you started
    It has no error handling and some of the code is a bit long-winded, but it does give the output you want.

    regards,

    StuartR
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    McLean, VA
    Posts
    125
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Macro to Export Comments and Tracked Changes (2003/SP1)

    Thanks, Stuart. I'm getting a "Compile Error: User-defined type not defined" at Dim xlApp As Excel.Application

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Macro to Export Comments and Tracked Changes (2003/SP1)

    Sorry about that, you need to set a reference to the Microsoft Excel 11.0 Object Library in Tools > References...

    StuartR

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    McLean, VA
    Posts
    125
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Macro to Export Comments and Tracked Changes (2003/SP1)

    I don't see a Tools>References. I do have Tools>Look Up Reference, which gives me only a Bookshelf 2000 option.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Export Comments and Tracked Changes (2003/SP1)

    Tools | References is a menu option in the Visual Basic Editor (where you create/edit your macro)

  9. #9
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Macro to Export Comments and Tracked Changes (2003/SP1)

    When you tried to compile the code, you were in the Visual Basic Editor, that has a Tools menu, with a References... menu item, as in this picture.

    StuartR
    Attached Images Attached Images
    • File Type: gif x.GIF (35.6 KB, 27 views)

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    McLean, VA
    Posts
    125
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Macro to Export Comments and Tracked Changes (2003/SP1)

    The macro works, but I'd like three modifications if possible:
    1. Instead of Section number, the heading number (e.g., 3.2.1.5) under which the comment/change is located (or the lowest heading number if it spans multiple heading numbers).
    2. Instead of the "overall" page number, the page number that appears in the footer where the comment/change is located (or the lowest such page number if multiple pages).
    3. The contents of the "target" (the selection upon which the comment/change is applied) of the comment/change. If it's an image, export "Image" to the spreadsheet. If it's a table, export "Table". I realize that a limitation to the size of the selection would be required (say the equivalent of 15-20 words) in case the target is large.

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro to Export Comments and Tracked Changes (

    I believe these are complicated requests, especially #3.

    Regarding #2, which I might be able to contribute, does your footer use any special calculations for the page number? For example, if you press Alt+F9 do you see {PAGE} or something more complicated?

  12. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    McLean, VA
    Posts
    125
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Macro to Export Comments and Tracked Changes (

    My page number in the footer uses the standard {PAGE} code.

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro to Export Comments and Tracked Changes (

    Try changing

    <code>revNext.Range.Information(wdActiveEndPageNum ber)</code>

    to

    <code>revNext.Range.Information(wdActiveEndAdjustedPageNumber)</code>

  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    McLean, VA
    Posts
    125
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Macro to Export Comments and Tracked Changes (

    I also had to change PageNumber to AdjustedPageNumber in the cmntNext statement. Now the macro exports the page number that appears in the footer, which is what I had asked for. Thanks, Jefferson! Is there any way to preserve the format of the page number (the roman numerals i, ii are appearing 1,2)?

  15. #15
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro to Export Comments and Tracked Changes (

    > Is there any way to preserve the format of the page number (the roman numerals i, ii are appearing 1,2)?

    You can retrieve the format for the main footer in the current section using this --

    selection.Sections(1).Footers(wdHeaderFooterPrimar y).PageNumbers.NumberStyle

    -- but how you would then translate 2 into ii seems to be more work than it's worth, unless there's a secret "roman numeral conversion" function that I don't know about.

    If you actually have a {PAGE} field in the footer and it is the first field, then you can get at the displayed value using this (omitting any bold, italic or other formatting, of course):

    selection.Sections(1).Footers(wdHeaderFooterPrimar y).Range.Fields(1).Result

    Oh wait, this only works if you do not have Different First Page footer. And probably Odd/Even will give bad results, too. Well...

Page 1 of 2 12 LastLast

Posting Permissions

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