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