Solved: Re: CopyBitmapToClipboard works sometimes but not ... - Qlik Community

happydays1967 · ‎2011-09-30

I have been digging through the forum and found some solutions to my problem. But now I am faced with a situation I cannot explain. Maybe somebody has experienced and solved this one....

Using: Excel 2007 and QV 10.00.9061.7 SR3

I am creating an Excel report from Qlikview using a vbscript macro inside QV. The QV has both pivot tables and pie charts, on several tabs that need to be exported to Excel in different sheets to create a monthly report. I have created bookmarks for (almost) every analyses in the QV, plus a few macro's that will calculate the right months selections.

So far so good, and everything works for the first tab and first sheet in Excel. Pivot tables are o problem at all, they export as a charm and I copy and paste them in the master report. But when I get to the second sheet in my QV, I have the same pie chart, but with a slightly different selection (different sales team). Now all of a sudden the .CopyBitmapToClipboard does not seem to work anymore. Either the previous analyses that was copied to the clipboard gets pasted as an image, or (when I clear the clipboard between two analyses) I get an Excel error telling me there is no image to paste.

Tried so far

- add the ActiveDocument.GetApplication.WaitForIdle in several places, which actually made the first sheet work

- clear the clipboard by copying an empty cell from excel between two analyses

- have a timer/wait loop in vbscript before calling the .CopyBitmapToClipboard method

All of which don't change a thing. The first set works, but the second and third run don't I activate the sheets that the objects are on. Make sure they are not minimised before I try the copy command. Doing things manually works, but through code I can't get it to work.

Anyone?

happydays1967 · ‎2011-09-30

Indeed, very important point. So, for everybody searching for a solution to the same problem:

For the .CopyBitmapToClipboard method of the chart-type sheetobject to work

- The sheet it resides on must be active

- the chart must be visible, i.e. NOT minimized

Some example code:

set oDoc = ActiveDocument

set oSht = oDoc.Sheets(i) ' where i is the index (counting from left to right, starting from 1) of the sheet your charts are on

oSht.Activate

oShtObjs = oSht.GetSheetObjects

for j = 0 to uBound(oShtObjs)

set oObj = oShtObjs(j)

sObjID = oObj.GetObjectID

if oObj.GetObjectType = 13 then

'To make it really tidy you could check if the object is already NOT minimized, but hey.....

oObj.Restore

ActiveDocument.GetApplication.WaitForIdle

oObj.CopyBitmapToClipboard

oObj.Minimize

end if

Report Inappropriate Content · ‎2011-09-30

Hello. I had something similar. Found a way to build:
ActiveDocument.GetSheetObject ("CH8-4"). Restore
Set obj4 = ActiveDocument.GetSheetObject ("CH8-4")
obj4.CopyBitmapToClipboard
ExcelDoc.Sheets (SheetName). PasteSpecial

happydays1967 · ‎2011-09-30

Ok, nevermind...... As it turns out, the .CopyBitmapToClipboard only works when the sheet the object is on is active. So when before starting to export from the next sheet activating this sheet did the job.

Something to be aware of, as exporting pivot tables to Excel works fine when the sheet they are on is not active.

kji · ‎2011-09-30

Not only does it need to be on the active sheet, it will also not work if the object is minimized.

happydays1967 · ‎2011-09-30