Runtime error 1004 application-defined or object-defined error



I tried to pose this on another group, MS Excel and VBA, but it
still has not been posted, and I realize now that this group is much
more active and accessible. . . .

I am getting the error: "Runtime error 1004 application-defined or
object-defined error" in the code provided below at this line of

.Cells(StartRow + n - 1, 5).Value = _

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 = _
' Page number
.Cells(StartRow + n - 1, 3).Value = _

'The text marked by the comment\
Debug.Print "Comment # " & n & " - Cell(" & StartRow
n - 1 & ", 5)"
Debug.Print "Cell value: " & .Cells(StartRow + n - 1,
Debug.Print "Comment scope: " & WordDoc.Comments(n).Scope
Debug.Print "--- reached end of scope"
.Cells(StartRow + n - 1, 5).Value = _
'The comment itself
Debug.Print "Getting the comment itself . . ."
.Cells(StartRow + n - 1, 6).Value = _
End With
Next n

' Application.ScreenUpdating = True
' Application.ScreenRefresh

MsgBox nCount & " comments found. Finished creating comments
document.", vbOKOnly, "Success!"

Set CommentWorkbook = Nothing
Set CommentSheet = Nothing
End Sub

Helmut Meukel

this may be a long shot. The debug.Print statement that works
transforms the Scope value into a string:
Debug.Print "Comment scope: " & WordDoc.Comments(n).Scope
Your code that chokes tries to assign it to the value property of the cell.
I would try something like:
.Cells(StartRow + n - 1, 5).Value = _
.Cells(StartRow + n - 1, 5).Value = _
"" & WordDoc.Comments(n).Scope

I'm not familiar with the Word Objects so I don't know what data/data type
Scope can contain, but if this is the problem one of the above should solve it.




Thanks for the suggestion. Unfortunately, that did not fix it.
It`s odd that this happens with one particular document, which does
not appear to have any other problems.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question