export word review comments in excel

Sorry, that should have been .Range.Duplicate. Code updated.

Please, no apologies. I'm still learning "Dim" :)

Run-time error '9': Subscript out of range

--> StrCmt = StrCmt & Split(.Paragraphs(1).Range.ListFormat.ListString, " ")(1) & vbTab

That's what happens when one starts out with one approach then switches to another along the way. Originally, I'd intended to output just the Heading Style's level number, since that's always populated whereas the heading won't necessarily be numbered. But, seeing as how you were already working with the heading numbering, I switched to the

.ListFormat.ListString approach - but did so clumsily. My original code at that point was:

StrCmt = StrCmt & Split(.Paragraphs(1).Style, " ")(1) & vbTab

It should now be:

StrCmt = StrCmt & .Paragraphs(1).Range.ListFormat.ListString & vbTab

Code in post updated.

At the top of the macro you will see the instruction

Sub CopyCommentsToExcel()

'Create in Word vba

'set a reference to the Excel object library

This means that from the VBA editor you should select Tools > References and check the box against Microsoft Excel. Otherwise Word will not know what to do with Excel commands

Extra Step:

I had to make the Developer tab visible, then click on Macro Security, and put a tick in the box

"Trust access to the VBA project object model".

I was then able to select the "Developer" tab, click on "Visual Basic" click on "Tools", and select the "References" (which was no longer greyed-out) as it had been prior to making the above changes.

Really useful, did exactly what I wanted.  So handy!  Many thanks.

Worked with MS Office Professional Plus 2010

2015-11-28

Extra Step:

I had to make the Developer tab visible, then click on Macro Security, and put a tick in the box

"Trust access to the VBA project object model".

I was then able to select the "Developer" tab, click on "Visual Basic" click on "Tools", and select the "References" (which was no longer greyed-out) as it had been prior to making the above changes.

Giving trusted access to the VBA project object model is both unnecessary for any of the macros discussed in this thread and is unwise for any situation where you don't need macros to do their own editing of your VBA environment. That setting allows macro viruses, for example, to write code to an Office application that could be used to wipe out all your data. Trusted access to the VBA project object model is not needed in order to set a reference to an Office application.