Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
gerhardl
Creator II
Creator II

Copy to Clipboard Fails (macro)

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

1 Solution

Accepted Solutions
gerhardl
Creator II
Creator II
Author

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.

View solution in original post

2 Replies
Anonymous
Not applicable

Hi

Try using application.waitforidle before copying the chart

Regards

Harsha

gerhardl
Creator II
Creator II
Author

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.