Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

PasteSpecial method of Worksheet class fails sometimes

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

5 Replies
Highlighted
Master II
Master II

Maybe the CopyTableToClipboard is taking awhile.

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

Highlighted
Creator II
Creator II

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

Highlighted
Master II
Master II

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

Highlighted
MVP & Luminary
MVP & Luminary

Highlighted
Master II
Master II

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