Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe the CopyTableToClipboard is taking awhile.
Try putting: ActiveDocument.GetApplication.WaitForIdle between the copy line and the pastespecial line
Ummm tried it, but the macro execution breaks on the:
ActiveDocument.GetApplication.WaitForIdle
I copied it straight from the API Guide and have used in many QV macros.
Maybe this helpful: Re: Sometimes. Macro paste method failed or incorrect format. Clipboard?
- Marcus
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