topic Re: copy to clipboard failed in QlikView

copy to clipboard failed

Wed, 25 Nov 2020 16:16:04 GMT

I m copying my qlikview tables through macro to excel sometime it shows me "Copy to Clipboard failed" I m able to find my mistake.Please help

Sub AAAA

Dim xlApp

Dim xlBook

Dim xlSheet

Dim xlNewSheet,intSheetCount

Dim wscount

Dim value

SET xlApp = CREATEOBJECT("Excel.Application")

xlApp.Visible = TRUE

SET xlBook = xlApp.Workbooks.Add

SET xlSheet = xlBook.Worksheets("Sheet1")

ws_count = ActiveDocument.NoOfSheets

FOR i=0 to ws_count-2

set Doc = ActiveDocument

set ss = Doc.Getsheet(i)

Call ss.Activate()

value = ss.GetProperties.Name

For j = 0 to ss.NoOfSheetObjects - 1

sheetobj = ss.SheetObjects(j).GetObjectId

set objType=Doc.GetSheetObject(sheetobj)

xlApp.Sheets(i+1).Range("A"&j*200+1).ClearContents

Call objType.CopyTableToClipboard(true)

xlApp.Sheets(i+1).Paste xlApp.Sheets(i+1).Range("A"&j*200+1)

Set WshShell = CreateObject("WScript.Shell")

WshShell.Run "cmd.exe /c echo. >NUL | clip", 0, True

xlApp.Sheets(i+1).Name = value

IF i < ws_count-2 THEN

SET xlNewSheet = xlApp.Application.Worksheets.Add(, xlApp.Worksheets(xlApp.Worksheets.Count))

xlApp.Worksheets(1).SELECT

END IF

xlBook.SaveAs("C:\Users\Hp\Desktop\ABC.xlsx")

xlBook.Close

End sub

Re: copy to clipboard failed

marcus_sommer — Sun, 14 Aug 2016 14:02:13 GMT

Take a look here: Re: copy clipboard failed and here: Re: Failed to copy to clipboard.

- Marcus

Re: copy to clipboard failed

Sun, 14 Aug 2016 14:19:06 GMT

Thanks @Marcus for your help.. But as u can see above i have tried both options in my code but with no luck

Re: copy to clipboard failed

marcus_sommer — Sun, 14 Aug 2016 16:42:15 GMT

Problems with the clipboard are usually difficult to solve. Maybe some WaitForIdle- and/or Sleep-Statements could be helpful, like:

....

xlApp.Sheets(i+1).Range("A"&j*200+1).ClearContents

ActiveDocument.GetApplication.WaitForIdle

Call objType.CopyTableToClipboard(true)

ActiveDocument.GetApplication.WaitForIdle

xlApp.Sheets(i+1).Paste xlApp.Sheets(i+1).Range("A"&j*200+1)

ActiveDocument.GetApplication.Sleep 2000

...

and I wouldn't Call a normal statement.

- Marcus

Re: copy to clipboard failed

Tue, 16 Aug 2016 05:13:30 GMT

Thanks a lot Marcus but with no luck.

Re: copy to clipboard failed

marcus_sommer — Tue, 16 Aug 2016 08:07:56 GMT

Then maybe with some trial & error like to suppress the error-message with something like:

Application.DisplayAlerts = False // it's VBA and not VBS

and/or to use:

On Error Resume Next

and to clear the clipboard again and/or copying any dummy-value in and clearing it again maybe in a loop x-times and with further sleep-statements or ....

- Marcus

Re: copy to clipboard failed

Sun, 21 Aug 2016 07:35:33 GMT

Hi Marcus..

Thanks Again Marcus.I appreciate ur support

But If i do Application.DisplayAlerts = False

it would not paste my object in excel as it is facing problem in Copying to ClipBoard.

And if i go for OnError Resume Next my loop will iterate. I guess i should send it to copy same object again and then paste with the help on On error Go to ...

Re: copy to clipboard failed

marcus_sommer — Mon, 22 Aug 2016 05:19:36 GMT

Unfortunately there are no complex error-handlings possible then the only supported error-statement within the qlikview vbs-implementation is On Error Resume next. In this sense was my suggestion not directly a solution else to play with various things to find a workaround. Your mentioned sending/exporting the objects to excel and copying them form there again together and/or to close the excel and quit the instance and then creating the excel and opening the application again between the various copying-statements or something similar might be helpful.

- Marcus

Re: copy to clipboard failed

beck_bakytbek — Mon, 22 Aug 2016 08:06:00 GMT

Hi Fabeha,

check this: QlikTip #32: Exporting multiple QV objects to a single Excel document

i am sure that this issue can contribute to solve your Problem

i hope that helps

beck