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

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

StuartR

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

Try changing

revNext.Range.Information(wdActiveEndPageNumber)

to

revNext.Range.Information(wdActiveEndAdjustedPageNumber)

Reply | Quote

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)?

Reply | Quote

> 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(wdHeaderFooterPrimary).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(wdHeaderFooterPrimary).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…

Reply | Quote

Thanks for all of your input, Jefferson. Fortunately the number of roman numeral pages my document have is not enough to worry about.

Reply | Quote

I see that Jefferson has already dealt with your request 2

To do 1 you would have to search backwards from the beginning of the revision range till you found a heading, then check if that heading was numbered and extract the number, it could be done but would take more time than I have before I go to bed tonight!

I don’t really understand Number 3, if there is an insertion then I show the Inserted text, if there is a deletion I show the deleted text. I guess you could show the Scope text for a Comment, instead of the comment text, or as well – this would be quite easy, just add the line
wbkOutput.Worksheets(1).Cells(iRow, 5) = Left(cmntNext.Scope.Text, 25)
after the rest of the similar lines for processing comments. This will add the first 25 characters of the Comment scope to the next column of the spreadsheet.

StuartR

Reply | Quote

I can survive without the paragraph heading column. You’re right about the Scope text not applying to tracked changes, but it’s really what I needed for Comments (I had to add a line to create the column header). Your macro is going to save a lot of time for my department – I appreciate all of your effort.

Reply | Quote

Once you have it perfected, I wouldn’t mind having a copy of the final product. Company policies permitting, of course.

Reply | Quote

I have attached the ExportComments macro with all of the modifications.

Reply | Quote

> I had to add a line to create the column header

Great, the only way to learn how to do this stuff is to try things and get them to work for you.

Thanks for the feedback. Please remember what I said earlier about error handling, I was too lazy to do this properly and when this macro goes wrong it won’t offer any indication of why.

StuartR

Reply | Quote