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
