copy to clipboard failed

[#1]

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

            Next

  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

  NEXT

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

xlBook.Close

End sub

[marcus_sommer]

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

- Marcus

[#3]

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

[marcus_sommer]

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

[#5]

Thanks a lot Marcus but with no luck.

[marcus_sommer]

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

[#7]

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 ...

[marcus_sommer]

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

[beck_bakytbek]

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