CODEWITHSUNDEEP
vbams-word
IrisRose
IrisRose

Reputation: 61

Macro Error - Microsoft Word - Run-time error '1004': Paste method of Worksheet class failed

I am trying to run a script that searches for a keyword, then copies the entire sentence where that keyword is found, and pastes it on an excel spreadsheet.

When I run the script on a document that is 1-2 pages, it runs fine, but when I am try a much longer document (100+ pages), I get the following error:

Run-time error '1004': Paste method of Worksheet class failed. When I click "debug" it says "objsheet.paste" is the problem.

Could you please help me fix the code so that it can work with longer text, too?

Sub FindWordCopySentence()
    Dim appExcel As Object
    Dim objSheet As Object
    Dim aRange As Range
    Dim intRowCount As Integer
    intRowCount = 1
    Set aRange = ActiveDocument.Range
    With aRange.Find
        Do
            .Text = "Hair"
            .Execute
            If .Found Then
                aRange.Expand Unit:=wdSentence
                aRange.Copy
                aRange.Collapse wdCollapseEnd
                If objSheet Is Nothing Then
                    Set appExcel = CreateObject("Excel.Application")
                                         Set objSheet = appExcel.workbooks.Open("C:\Users\HNR\Desktop\hair.xlsx").Sheets("Sheet1")
                    intRowCount = 1
                End If
                objSheet.Cells(intRowCount, 1).Select
                objSheet.Paste
                intRowCount = intRowCount + 1
            End If
        Loop While .Found
    End With
    If Not objSheet Is Nothing Then
        appExcel.workbooks(1).Close True
        appExcel.Quit
        Set objSheet = Nothing
        Set appExcel = Nothing
    End If
    Set aRange = Nothing
End Sub

Upvotes: 1

Views: 645

Answers (1)

YowE3K
YowE3K

Reputation: 23974

If the issue is due to copy/pasting the information, that can be avoided by just assigning the text directly:

Sub FindWordCopySentence()
    Dim appExcel As Object
    Dim objSheet As Object
    Dim aRange As Range
    Dim intRowCount As Integer
    Dim myTempText As String
    intRowCount = 1
    Set aRange = ActiveDocument.Range
    With aRange.Find
        Do
            .Text = "Hair"
            .Execute
            If .Found Then
                aRange.Expand Unit:=wdSentence
                'Store the text into a variable
                myTempText = aRange.Text
                aRange.Collapse wdCollapseEnd
                If objSheet Is Nothing Then
                    Set appExcel = CreateObject("Excel.Application")
                    Set objSheet = appExcel.workbooks.Open("C:\Users\HNR\Desktop\hair.xlsx").Sheets("Sheet1")
                    intRowCount = 1
                End If
                'Set the destination cell to the text we stored
                objSheet.Cells(intRowCount, 1).Value = myTempText
                intRowCount = intRowCount + 1
            End If
        Loop While .Found
    End With
    If Not objSheet Is Nothing Then
        appExcel.workbooks(1).Close True
        appExcel.Quit
        Set objSheet = Nothing
        Set appExcel = Nothing
    End If
    Set aRange = Nothing
End Sub

Another potential cause of the issue is if you were bored while processing the large document, so you left it running in the background while doing other copy/paste operations on something else.

Copy and Paste share the clipboard with other applications so, if you do a copy between when the code did its Copy and when it did its Paste, it will be trying to Paste what you copied instead of what it copied.

So, whenever possible, avoid using Copy/Paste within code.

Upvotes: 2

Related Questions