How can I prevent a non-fatal runtime error from occurring when pasting text from a Word document to Excel using VBA?

Question

I export two strings of text from an Excel spreadsheet to Word, use the Word Compare function to highlight and underline the differences between the two, and then export that final string with the formatting back to the Excel spreadsheet.

When this code runs down the column, sometimes the ActiveSheet.Paste line, gives me

Run-time error '1004':
Microsoft Excel cannot paste the data.

Dim previous As String: previous = Cells(i, 19).Value
Dim current As String: current = Cells(i, 20).Value

Dim wordApp As Word.Application: Set wordApp = New Word.Application
wordApp.Visible = True

Dim firstdoc As Word.Document: Set firstdoc = wordApp.Documents.Add
firstdoc.Paragraphs(1).Range.Text = previous

Dim seconddoc As Word.Document: Set seconddoc = wordApp.Documents.Add
seconddoc.Paragraphs(1).Range.Text = current

Dim lastdoc As Word.Document
Set lastdoc = wordApp.CompareDocuments(firstdoc, seconddoc, wdCompareDestinationNew)
With lastdoc.ActiveWindow.View.RevisionsFilter
    .Markup = wdRevisionsMarkupAll
    .View = wdRevisionsViewFinal
End With

lastdoc.Content.FormattedText.Copy

Cells(i, 20).Activate
Cells(i, 20).Select
PAUSE 3
ActiveSheet.Paste 'Where the program always stops for some reason.

firstdoc.Close SaveChanges:=wdDoNotSaveChanges
seconddoc.Close SaveChanges:=wdDoNotSaveChanges
lastdoc.Close SaveChanges:=wdDoNotSaveChanges
wordApp.Visible = False

When I hit debug and F5 (Continue), it begins to work again like normal. If I have 30 rows of text, this might occur 5-6 times throughout the program execution. I know it has nothing to do with the extent of text it's handling because this error occurs randomly down the row, sometimes when pasting a large block of text or sometimes pasting a small block of text.

Someone suggested that I use the PAUSE 3 Subroutine to slow down the program for Excel to catch up. It did decrease the frequency of the error message.

What could be going on and how do I fix it?

Sub PAUSE(Period As Single)
Dim t As Single
Period = 0.5
t = Timer + Period
Do
    DoEvents
Loop Until t < Timer
End Sub

Here's an example with an approach which typically works for me: https://stackoverflow.com/a/60582628/478884 — Tim Williams, Jun 27 '22 at 17:23
@TimWilliams Is this only if I use a clipboard? Also, I'm not sure I understand what the code is trying to do. Isn't it just skipping the rows that have an error and moving on to the next one? — ARJ, Jun 27 '22 at 17:31

Tim Williams · Answer 1 · 2022-06-27T18:29:00.097

0

You can retry the paste if it fails - this has always worked for me when a single attempt to paste was failing sometimes.

Replace this:

lastdoc.Content.FormattedText.Copy

Cells(i, 20).Activate
Cells(i, 20).Select
PAUSE 3
ActiveSheet.Paste 'Where the program always stops for some reason

with:

    Dim n As Long, pasted As Boolean
    '...
    '...
    lastdoc.Content.FormattedText.Copy
    pasted = False                'reset paste status flag
    For n = 1 To 10               'try 10 times to paste
        On Error Resume Next      'ignore any paste error
        ActiveSheet.Paste Destination:=ActiveSheet.Cells(i, 20)
        pasted = (Err.Number = 0) 'no error = pasted OK
        On Error GoTo 0           'stop ignoring errors
        If pasted Then Exit For   'exit if pasted OK
        DoEvents
    Next n
    
    If Not pasted Then 'was there a problem pasting?
        MsgBox "Problem pasting!"
    End If
    '...
    '...

edited Jun 27 '22 at 18:29

answered Jun 27 '22 at 18:03

Tim Williams

154,628
8
97
125

Would this go in another subroutine as an error handler or in the main private sub? Also, if "i" goes from 1 to 10, then doesn't that mean that each time it's retrying to paste, it's telling the code to paste in the next row? What if I want the program to retry pasting in the same cell? – ARJ Jun 27 '22 at 18:10
1

Sorry I forgot you were already using `i` - swapped that for `n`. Edited to show what the code replaces. – Tim Williams Jun 27 '22 at 18:29
It does work, but I did get the problem pasting message a couple of times. Does the frequency of the problem decrease with a greater number of re-trys? – ARJ Jun 27 '22 at 19:08
I guess it's just a matter of statistics - the more times your `i` loop runs, the higher the chance that the paste will fail, even after 10 tries. You could try increasing that 10 to 20 and see if that resolves anything. – Tim Williams Jun 27 '22 at 19:11
Also, do you think the failure rate could also be decreased by adding the PAUSE 3 subroutine? I'm still not sure why the paste fails, but maybe it could be the processing speed of the code vs the spreadsheet? – ARJ Jun 27 '22 at 19:16
You can try that - would not harm. – Tim Williams Jun 27 '22 at 19:29

How can I prevent a non-fatal runtime error from occurring when pasting text from a Word document to Excel using VBA?

1 Answers1