VBA PasteSpecial error in ActiveSheet.PasteSpecial

Question

I have a macro to open a Word document in the backend and pull all the data tables into an Excel template.

When I tested it in some of my colleagues' machines, they get a "VBA PasteSpecial error".

I checked the references and add ins in my colleagues' machines are that of mine.

Dim sht As Worksheet
Dim WordDoc As Word.Document
Dim WordApp As Word.Application
Dim i As Long, r As Long, c As Long
Dim rng As Range, t As Word.Table
Dim ia As Integer
Dim OpenForms
Dim target As Range
ia = 1
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = False
Set WordDoc = WordApp.Documents.Open(DOC_PATH, ReadOnly:=True)
Set sht = Sheets("test")
Set rng = sht.Range("A5")
sht.Activate
For Each t In WordDoc.Tables
    OpenForms = DoEvents
    t.Range.Copy
    OpenForms = DoEvents
    ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Table_" & ia ' add new sheet
    Range("a1").Select ' paste table
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    OpenForms = DoEvents

Answer 1

EDIT

The OP diagnosed the problem as being a concurrency issue where the clipboard was not copied to in time for the paste operation. The code below will solve this issue while maintaining a responsive UI and reasonable timeout and notification.

On Error Resume Next
Dim TimeoutLimit
TimeoutLimit = 300
Dim TimeoutCounter
TimeoutCounter = 0
Do
    Err.Clear
    DoEvents 'Yield thread execution
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    TimeoutCounter = TimeoutCounter + 1
Loop Until (Err.Number = 0 Or TimeoutCounter > TimeoutLimit )
On Error GoTo 0
If TimeoutCounter > TimeoutLimit Then
    MsgBox "Paste failed because of operation timeout", vbCritical
    'Is this fatal? Abort by proper exiting...
    'Exit Sub
    'Exit Function
End If

---

I think your use of ActiveSheet.PasteSpecial is probably fine, based on the testing you've done and this MSDN Documentation. However, your problem might be that you're getting data incompatable with a text format as discussed below.

Regarding the method, the Worksheet.PasteSpecial method is entirely different from the Range.PasteSpecial. I find it odd that the error you're getting says that a range method failed if you're calling the worksheet method. If that's accurate, I suspect that the Worksheet method calls the range method at some point.

I can reproduce the specific error

PasteSpecial method of Range class failed

for Range.PasteSpecial under the following conditions:

• the clipboard is empty
• the data was not copied from an Office Application.

Turns out, there is an office clipboard and a Windows clipboard. For instance, Application.CutCopyMode only controls the office clipboard. Range.PasteSpecial works off of the Office clipboard, while Workbook.PasteSpecial works off of the system or Windows clipboard. So it doesn't throw error if the office clipboard is empty, in fact, it throws an error for the opposite condition where an excel range is copied if it's being forced to use Text as its format.

PasteSpecial method of Worksheet class failed

It also throws this error if the clipboard data cannot be converted to text (like a picture). You can handle this by not specifying a format and a default format will be used. It won't necessarily be text then, but this resolves the error for pasting an Excel Range.

To illistrate this and check to see how your clipboard content is stored, view it from Excel as shown below.

Since it is possible that one thing or the other might be giving you different problems under different conditions, you could try to progress through your options like this...

On Error Resume Next
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
If Err > 0 Then
    Err.Clear
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    If Err > 0 Then
        Err.Clear
        'You could also try to manually retrieve data from clipboard at this point
        ActiveSheet.PasteSpecial
    End If
End If
On Error GoTo 0

Like I said in my comments, PasteSpecial can be finicky. Thus I reccomended removing that as a factor and testing if you can directly access clipboard content like the code below (copied from here)...

Sub GetClipBoardText()
   Dim DataObj As MSForms.DataObject
   Set DataObj = New MsForms.DataObject '<~~ Amended as per jp's suggestion

   On Error GoTo Whoa

   '~~> Get data from the clipboard.
   DataObj.GetFromClipboard

   '~~> Get clipboard contents
   myString = DataObj.GetText(1)
   MsgBox myString

   Exit Sub
Whoa:
   If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Sub

See this documented case of the same error that was being caused by the clipboard being empty and how easily this can happen with Office Macros. You are copying within your macro, so I wouldn't expect this to be your problem. Also, this code snippet will protect against a null for the Range method but not for the Worksheet method, since it only checks the application's clipboard and not the system's.

If Application.CutCopyMode = True Then
  ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Else
  MsgBox("There is no data on the clipboard to be pasted.")
End If