Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
happydays1967
Creator
Creator

CopyBitmapToClipboard works sometimes but not always

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?

1 Solution

Accepted Solutions
happydays1967
Creator
Creator
Author

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

next

View solution in original post

5 Replies
Not applicable

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
Creator
Creator
Author

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

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

happydays1967
Creator
Creator
Author

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

next

View solution in original post

murozel76
Contributor III
Contributor III

Very good thread and a perfect answer! Thank you very much for this. I experienced the very same problem and have just found the answer here.

Best Regards,