Copy to Clipboard Fails (macro)

gerhardl — Thu, 18 Dec 2014 06:04:00 GMT

Hi,

I have a macro which is supposed to be very basic. It copies and pastes 3 different straight table charts' data to excel, then I need to make a selection in a field and paste the same tables with the updated data in a second sheet.

The part from making the selections onwards I haven't even gotten to because for some crazy reason I keep getting a "copy to clipboard failed" error when trying to copy and paste the 3 objects' values.

I've tried commenting out pieces of the code to isolate the problem. If I copy and paste them 1 by 1 it is fine. If I copy CH01 and CH03 but comment out CH02 it's fine too. CH02 on its own is fine too, but when combining it with one or both of the others it fails. CH02 is identical to the others in all formatting etc. except that the number format is different, but even when I make them all the same format it fails.

Maybe I should clear the clipboard memory in-between pastes? I am lost.

Here is my code, and attached are images showing the charts and the error:

Sub Run_Report_1

dtmDate = Date

strYear = year(Date()-8)

strMonth = month(Date()-8)

if len(strMonth) = 1 then strMonth = "0" & strMonth

strDay = day(Date()-8)

if len(strDay) = 1 then strDay = "0" & strDay

strYesterYear = year(Date()-1)

strYesterMonth = month(Date()-1)

if len(strYesterMonth) = 1 then strYesterMonth = "0" & strYesterMonth

strYesterDay = day(Date()-1)

if len(strYesterDay) = 1 then strYesterDay = "0" & strYesterDay

'---------------------------- Sheet 1: Trading Calendar Month to Date ----------------------------------'

SET XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

SET XLDoc = XLApp.Workbooks.Open ("C:\Users\tenba1\Documents\QlikView\Cross Shopping\Cross Shopping Report - " & strYear & "-" & strMonth & "-" & strDay & ".xlsx")

SET XLSheet1 = XLDoc.Worksheets(1)

ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard true

XLSheet1.Paste XLSheet1.Range("B4")

ActiveDocument.GetSheetObject("CH02").CopyTableToClipboard true

XLSheet1.Paste XLSheet1.Range("B14")

ActiveDocument.GetSheetObject("CH03").CopyTableToClipboard true

XLSheet1.Paste XLSheet1.Range("B24")

XLApp.Visible = False

XLDoc.SaveAs "C:\Users\tenba1\Documents\QlikView\Cross Shopping\Cross Shopping Report - " & strYesterYear & "-" & strYesterMonth & "-" & strYesterDay & ".xlsx"

XLApp.Quit

End Sub

Re: Copy to Clipboard Fails (macro)

Anonymous — Thu, 18 Dec 2014 06:33:39 GMT

Try using application.waitforidle before copying the chart

Regards

Harsha

Re: Copy to Clipboard Fails (macro)

gerhardl — Thu, 18 Dec 2014 07:02:01 GMT

Hi,

This didn't work, but I just figured out the problem (by pure luck).

The computer with the QV licence is separate to my laptop, and I was accessing it and working on the QV module remotely from my laptop. To do this I used TeamViewer, and TeamViewer has a setting to keep the clipboards to the 2 computers in synch. This confused the hell out of QlikView I guess.

Turned off this setting and it works fine.

topic Re: Copy to Clipboard Fails (macro) in QlikView

Copy to Clipboard Fails (macro)

Re: Copy to Clipboard Fails (macro)

Re: Copy to Clipboard Fails (macro)