+ Reply to Thread
Results 1 to 26 of 26

Word Macro to Extract Track Changes to Excel

  1. #1
    Registered User
    Join Date
    11-18-2013
    Location
    Indi
    MS-Off Ver
    Excel 2007
    Posts
    9

    Word Macro to Extract Track Changes to Excel

    Hi,

    I am looking for a Word Macro that would extract each trackchanges in Word to a separate cell in a column of excel. When I googled, I got a macro, but it is not extracting the original word. For example, if owl was deleted and changed to low, I want owl in original column and low in latest column. I want each of the track changes in separate cells.

    Your help would be really appreciated.

    Thank you for looking into this!

  2. #2
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    See my posts at: http://answers.microsoft.com/en-us/o...f-8dc609cc75af
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    11-18-2013
    Location
    Indi
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Word Macro to Extract Track Changes to Excel

    I saw the post and it is very useful. Thank you!

    Will it be possible to add 2 more columns for it to be more useful to reader, which would mention in which sentence the change was made and the revised sentence.

    For example, if the actual sentence is "This is Topsy." which was changed to "This is Turvy". If we can show Original sentence as well as the corrected sentence it would be very helpful to learn.

    If possible, I just need 4 columns, s.no, page #, original sentence, corrected sentence. If a sentence has more than one track change, it would be very useful if the sentence is displayed only once and not multiple times. We also don't want any changes in format to be included.

    Kindly help.

  4. #4
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    The macro already shows both the old (deleted) and new (inserted) content on consecutive rows, I'm not sure what you think would be achieved, especially when it comes to entire paragraphs (or more) being replaced. Besides which, Word's VBA has no idea what a grammatical sentence is. For example, consider the following:

    Mr. Smith spent $1,234.56 at Dr. John's Grocery Store, to buy: 10.25kg of potatoes; 10kg of avocados; and 15.1kg of Mrs. Green's Mt. Pleasant macadamia nuts.

    For you and me, that would count as one sentence; for VBA it counts as 5...

  5. #5
    Registered User
    Join Date
    11-18-2013
    Location
    Indi
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Word Macro to Extract Track Changes to Excel

    As always, thanks for the clear explanation. Will it be possible to just extract the sentence (not grammatical), the original and the corrected. In the above eg, if 10.25kg of potatoes was corrected to 10.35kg of potatoes, will it be possible to extract 10.25kg of potatoes in one column and 10.35kg of potatoes in another column along with s.no.

  6. #6
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    As I have already said, such changes already appear on consecutive rows. Besides which, what would a macro be expected to do if '10.25kg of potatoes' was changed to '10.35kg of potatoes', then to '10.35kg of potatoes', then to '10.25kg of sweet potatoes', before being moved to another location - or deleted altogether? Similarly, a tracked change that consists of just an insertion has no 'before' text to show - just as a deletion has no 'after' text to show.

  7. #7
    Registered User
    Join Date
    11-18-2013
    Location
    Indi
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Word Macro to Extract Track Changes to Excel

    Thank you! Now I can understand.

  8. #8
    Registered User
    Join Date
    11-08-2018
    Location
    Barre, VT
    MS-Off Ver
    2016
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    Hi, When I run your macro in 2016 I get the error "Compile error: User-defined type not defined" with "xlApp As New Excel.Application" highlighted. Please help - your macro is going to save me a LOT of time! Thank you.exportmacroerror.png

  9. #9
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    Please read the comments at the top of the code...

  10. #10
    Registered User
    Join Date
    11-08-2018
    Location
    Barre, VT
    MS-Off Ver
    2016
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    I see the note now, but I am a complete novice when it comes to Macros; could you spell it out for me?

  11. #11
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    In the VBE, go to Tools|References, scroll down to the Microsoft Excel entry and check it.

  12. #12
    Registered User
    Join Date
    11-08-2018
    Location
    Barre, VT
    MS-Off Ver
    2016
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    Ok, I think I did, but still getting same error?check.png

  13. #13
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    It looks to me like you added the reference to your 'Town of Barre' document, but the macro is in Word's Normal template... The reference must be added to the file the macro resides in.

  14. #14
    Registered User
    Join Date
    11-08-2018
    Location
    Barre, VT
    MS-Off Ver
    2016
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    You're right! That fixed that one, but while running another issue came up, see below. I really appreciate your help!
    error3.png

  15. #15
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    And what was the error message associated with that?

  16. #16
    Registered User
    Join Date
    11-08-2018
    Location
    Barre, VT
    MS-Off Ver
    2016
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    "5852: Requested object is not available"

  17. #17
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    It's not apparent to me why you'd get such an error with that line. Try changing it to:
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    11-08-2018
    Location
    Barre, VT
    MS-Off Ver
    2016
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    Same error, but a bit further down? Is it the way I pasted it in?
    error4.png

  19. #19
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    I cannot imagine why you'd be getting such errors. The code works fine for me - and you're the first to ever suggest a problem with it. Either there's a problem with your Office installation (try repairing it, via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair) or there's something odd about how it's been copied into your VBE.

  20. #20
    Registered User
    Join Date
    11-08-2018
    Location
    Barre, VT
    MS-Off Ver
    2016
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    Well I really appreciate you spending so much time with me on this today. I'll try the office repair.

  21. #21
    Registered User
    Join Date
    08-23-2019
    Location
    Boston, MA
    MS-Off Ver
    16
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    Hi macropod,

    Thank you for the code. I'm looking for something similar, too. I have two questions, please.

    First, all of my changes are showing up in the "Delete" column. I guess I thought if you replaced a word, it would show up in the "Replace" column (H).

    Secondly, from your post, it looks like there may be a way to show the entire paragraph, before and after? If that's true, would you mind providing some guidance how to accomplish that, please?

    I am attaching a dummy document for illustration and the corresponding Excel document.

    Sincerely,
    Roy
    Attached Files Attached Files

  22. #22
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel


  23. #23
    Registered User
    Join Date
    08-23-2019
    Location
    Boston, MA
    MS-Off Ver
    16
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    Thank you, macropod. What I was hoping for is to capture the entire paragraph (before vs after). I had seen in one of your previous post that you thought this might be possible.

  24. #24
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Word Macro to Extract Track Changes to Excel

    While it would be possible to capture entire paragraphs, that'd:
    a) make it quite difficult to see what the change was, since it would be indistinguishable from the rest of the captured content; and
    b) result in the same paragraph being captured over and over for each separate tracked change within it. Imagine a paragraph with 10 edits - some as additions, deletions, formatting, etc...

  25. #25
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Word Macro to Extract Track Changes to Excel

    scienceguyroy Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  26. #26
    Registered User
    Join Date
    08-23-2019
    Location
    Boston, MA
    MS-Off Ver
    16
    Posts
    7

    Re: Word Macro to Extract Track Changes to Excel

    Thank you, macropod, for the follow-up. This make sense.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract to Excel from Word Document with track change mode
    By ShailShin in forum Word Formatting & General
    Replies: 4
    Last Post: 07-02-2014, 06:31 PM
  2. Need a macro to extract track changes and comments from Word to Excel
    By serene11111 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-13-2014, 06:35 PM
  3. [SOLVED] Excel macro to extract Word form data into Excel
    By Phillipe in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-01-2012, 10:55 PM
  4. Macro error - to extract first and second word from a string
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-20-2011, 12:23 PM
  5. Extract a text from word and place it en excel or word?
    By Elegidos in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2009, 06:14 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1