PasteSpecial method of Worksheet class fails sometimes

[Anonymous]

I'm having a headache with a macro to export the data from some pivot tables to an  Excel template. It throws a "PasteSpecial method of Worksheet class failed" from time to time, not always, but I don't understand why the error is not consistent. Can anyone help me with figuring out what I'm doing wrong?
Here's the macro:

sub ExcelTable1TemplateUse

Dim CountPasteSpecialErrors

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

set XLDoc = XLApp.Workbooks.Open("C:\Users\BSS\Documents\Alfaintes\20171204 - Report prioritari\Reports\Tabel 1\Table1_Template_SingleUser.xlsx")

XLDoc.Sheets(1).Select

XLDoc.Sheets(1).Range("A1").Select

ActiveDocument.GetSheetObject("CH128").CopyTableToClipboard true

XLDoc.Sheets(1).PasteSpecial -4163

XLDoc.Sheets(1).Range("X1").Value = err.Number

XLDoc.Sheets(1).Range("A1:C1").ClearContents

XlDoc.Sheets(1).Range("A1:C1").Merge

XLDoc.Sheets(1).Range("A2").Value = XLDoc.Sheets(1).Range("A3").Value & " " & XLDoc.SHeets(1).Range("B3").Value

XLDoc.Sheets(1).Range("B2:B10,A3:A10").ClearContents

XLDoc.Sheets(1).Range("A2:B2").Merge

end sub

[m_woolf]

Maybe the CopyTableToClipboard is taking awhile.

Try putting: ActiveDocument.GetApplication.WaitForIdle between the copy line and the pastespecial line

[Anonymous]

Ummm tried it, but the macro execution breaks on the:
ActiveDocument.GetApplication.WaitForIdle

[m_woolf]

I copied it straight from the API Guide and have used in many QV macros.

[marcus_sommer]

Maybe this helpful: Re: Sometimes. Macro paste method failed or incorrect format. Clipboard?

- Marcus

[m_woolf]

I tried your code and it repeatedly failed at pastespecial. After trying in vain to fix, I came up with:

sub ExcelTable1TemplateUse

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

Set XLDoc = XLApp.Workbooks.Add

set XLSheet = XLDoc.Sheets(1)

XLSheet.Select

XLDoc.Sheets(1).Range("A1").Select

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

ActiveDocument.GetApplication.WaitForIdle

XLSheet.Paste   'Special -4163

XLApp.Selection.ClearFormats

end sub