topic Re: Macros. CopyBitmapToClipboard fails in QlikView

Macros. CopyBitmapToClipboard fails

MindaugasBacius — Fri, 17 Feb 2017 14:24:22 GMT

Hello,

Why could this script fail:

XLApp.Sheets(1).Range("A27").Select

ActiveDocument.GetSheetObject("eur").CopyBitmapToClipboard TRUE

XLApp.Sheets(1).PasteSpecial Format = Bitmap

The error that is given:

Wrong number of arguments or invalid property assignment: 'ActiveDocument.GetSheetObject(...).CopyBitmapToClipboard'

When I change from CopyBitmapToClipboard to CopyTableToClipboard macros executes just fine, only I do get the table not a chart.

Could anybody please help me out?

Thank you!

Re: Macros. CopyBitmapToClipboard fails

marcus_sommer — Fri, 17 Feb 2017 15:08:12 GMT

Why do you want to use pastespecial by an image instead of just paste?

- Marcus

Re: Macros. CopyBitmapToClipboard fails

MindaugasBacius — Mon, 20 Feb 2017 06:25:35 GMT

I do not have any comments on that. I did change it to Paste

Unfortunately I guess that's not the case because while running the code it highlights this line:

ActiveDocument.GetSheetObject("eur").CopyBitmapToClipboard TRUE

Re: Macros. CopyBitmapToClipboard fails

marcus_sommer — Mon, 20 Feb 2017 07:36:48 GMT

AFAIK copybitmaptoclipboard hadn't a further parameter TRUE/FALSE because it's only a switch by table-charts and copytabletoclipboard. Beside them you need to ensure that "eur" is really a valid object-id and that this object is currently displayed within qlik - if it's hidden in a container or by a condition or minimized it won't work.

- Marcus

Re: Macros. CopyBitmapToClipboard fails

MindaugasBacius — Mon, 20 Feb 2017 08:02:00 GMT

The chart is not hidden or minimized, the object ID is valid:

I am also using copytabletoclipboard as well as copybitmaptoclipboard thats why I am using the parameter TRUE/FALSE. By removing it nothing changes the script stops on the same line but without showing any error.

Re: Macros. CopyBitmapToClipboard fails

MindaugasBacius — Mon, 20 Feb 2017 08:14:23 GMT

Although the script seems to work when leaving everything the same except changing CopyBitmapToClipboard to CopyTableToClipboard.

Re: Macros. CopyBitmapToClipboard fails

marcus_sommer — Mon, 20 Feb 2017 08:15:00 GMT

If a qlik macro failed the cursor-position within the macro-editor isn't mandatory by the error and might therefore leading in wrong direction. Put a few msgbox in your code to find the real place where the error happens. If this didn't help please provide an example.

- Marcus

Re: Macros. CopyBitmapToClipboard fails

MindaugasBacius — Mon, 20 Feb 2017 09:05:55 GMT

		ActiveDocument.Sheets(0).Activate
		XLApp.Sheets(1).Select
		MsgBox "This is fun"
		ActiveDocument.GetSheetObject("kg").CopyBitmapToClipboard
		'MsgBox "This is fun2"
		XLApp.Sheets(1).Range("A27").Select
		XLApp.Sheets(1).PasteSpecial Format = Bitmap

With the MsgBox "This is fun" everything works fine. But when I comment it everything stops again. How to understand this? What am I missing?

Re: Macros. CopyBitmapToClipboard fails

marcus_sommer — Mon, 20 Feb 2017 09:21:54 GMT

That's strange - maybe something is still processing and not yet finished if you call your routine respectively between the various statements within the routine. To resolve this you could use the following statements:

ActiveDocument.GetApplication.WaitForIdle

rem ** let QV sleep for 1 seconds **

ActiveDocument.GetApplication.Sleep 1000

Beside them I think I wouldn't rather use a selection-statement to the sheet:

XLApp.Sheets(1).Select

else just activating the sheet, select a certain range and than paste your content maybe in this way:

ActiveDocument.Sheets(0).Activate

ActiveDocument.GetSheetObject("kg").CopyBitmapToClipboard

XLApp.Sheets(1).Activate

XLApp.Sheets(1).Range("A27").Select

XLApp.Sheets(1).Paste

- Marcus

Re: Macros. CopyBitmapToClipboard fails

MindaugasBacius — Mon, 20 Feb 2017 11:18:16 GMT

I am stopped by this issue.

Could you please check the example attached?

Re: Macros. CopyBitmapToClipboard fails

marcus_sommer — Mon, 20 Feb 2017 13:12:16 GMT

This worked. The main-issue was the missing sheet-reference within excel but there was also a not assigned variable and no store of the excel.

- Marcus

Re: Macros. CopyBitmapToClipboard fails

MindaugasBacius — Mon, 20 Feb 2017 13:53:44 GMT

Thank you!

Seems good.

Could you please explain what's the logic behind this?

When I copy the line with "kg" to the middle in the script the code do not work. It works when its above of every SET sentence.

Re: Macros. CopyBitmapToClipboard fails

marcus_sommer — Mon, 20 Feb 2017 14:35:27 GMT

The intention to put the statement above the excel-stuff was just to separate them and to keep a better overview over the code.

And of course this hasn't any influence as far as there are no further copy-statements between it and the paste-statement - this meant in general you could mix up qlikview-statements and others. On the other hand it's a very good example of confusing the user by highlighting the copy-statement instead of doing nothing or even better of displaying the real error - which is a store-error respecitively the dialog-question (will be only shown by executing the routine with a button from the UI) that the file already exists and if you want to overwrite them.

If you don't get an understandable error you could also use On Error Resume Next within your routine to force to continue even after an error and then to query the error per err.number & ' - ' & err.description.

For this I have the routine a bit extended to a storing-sub-routine which will at first delete an existing older file and storing the new one.

SUB ExcelFile

set doc = ActiveDocument

vPath = "C:\Users\Mindaugasb\Desktop\"

reportFile = vPath & "Test" & ".xlsx"

doc.ClearAll

doc.GetSheetObject("kg").CopyBitmapToClipboard

SET XLApp = CreateObject("Excel.Application")

XLApp.Visible = TRUE

SET XLDoc = XLApp.Workbooks.Add

Set XLSheet = XLDoc.ActiveSheet

XLSheet.Range("A1").Select

XLSheet.Paste

call OverwriteExistingFile(reportFile, XLDoc)

'XLDoc.Saveas reportFile

XLApp.Quit

END SUB

sub OverwriteExistingFile(File, Excel)

set fso = CreateObject("Scripting.FileSystemObject")

fso.DeleteFile(File)

Excel.Saveas File

end sub

- Marcus