I am getting the error: "Runtime error 1004 application-defined or
object-defined error" in the code provided below at this line of code:
.Cells(StartRow + n - 1, 5).Value = _
WordDoc.Comments(n).Scope
It is processing comments in a Word document and outputting them into
Excel cells. It works fine on another document, but not on this
particular document. However, the debug statements I inserted prior
to this (see code below) output fine.
Anyone have any idea what might cause this?
Thanks, Alan
Public Sub Extract(WordApp As Object, WordDoc As Object)
Const StartRow = 2
Dim CommentWorkbook As Workbook, CommentSheet As Worksheet
Dim nCount As Long
Dim n As Long
' Set up access to the Excel worksheet
Set CommentWorkbook = ActiveWorkbook
Set CommentSheet = CommentWorkbook.Worksheets("Comments")
' Make sure the right worksheet can be accessed
If CommentSheet Is Nothing Then
Error.ShowErrorMsg "Unable to find the worksheet named
'Comments'", "Excel Worksheet Error"
GoTo SafeExit
End If
' Determine how many comments are in the Word document
nCount = WordDoc.Comments.Count
' If no comments are found, exit
If nCount = 0 Then
Error.ShowErrorMsg "The active document contains no
comments!", "No Comments Found", "ExtractWordComments.Extract"
GoTo SafeExit
End If
' Application.ScreenUpdating = False
'Get info from each comment in WordDoc, and insert in spreadsheet
For n = 1 To nCount
With CommentSheet
' Comment number
.Cells(StartRow + n - 1, 1).Value = n
' Author name
.Cells(StartRow + n - 1, 2).Value = _
WordDoc.Comments(n).Author
' Page number
.Cells(StartRow + n - 1, 3).Value = _
WordDoc.Comments(n).Scope.Information(Word.wdActiveEndPageNumber)
'The text marked by the comment\
Debug.Print "Comment # " & n & " - Cell(" & StartRow +
n - 1 & ", 5)"
Debug.Print "Cell value: " & .Cells(StartRow + n - 1,
5).Value
Debug.Print "Comment scope: " & WordDoc.Comments(n).Scope
Debug.Print "--- reached end of scope"
.Cells(StartRow + n - 1, 5).Value = _
WordDoc.Comments(n).Scope
'The comment itself
Debug.Print "Getting the comment itself . . ."
.Cells(StartRow + n - 1, 6).Value = _
WordDoc.Comments(n).Range.Text
End With
Next n
' Application.ScreenUpdating = True
' Application.ScreenRefresh
CommentSheet.Activate
MsgBox nCount & " comments found. Finished creating comments
document.", vbOKOnly, "Success!"
SafeExit:
Set CommentWorkbook = Nothing
Set CommentSheet = Nothing
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
