topic Re: Qlikview export to PowerPoint in QlikView

Qlikview export to PowerPoint

Thu, 23 Jul 2015 15:09:48 GMT

Hello,

I realise how many of those topic was here already. Somehow I cannot get exactly what I need, so I decided to ask.

Can someone please advice?

I need to export some objects from qlikview into ppt. The ppt already exist and specific objects must go to a specific slide.

The problem I have is when the all screen is pasted into PowerPoint the image itself is larger than a content of a screen.

I have everything what was in qlikview plus huge white border around it.

Also not sure how to resize it properly. Can you please advice?

Here is my code:

Sub ExportPPT

Set PPApp = CreateObject("Powerpoint.Application")
PPApp.Visible = True
Set PPPres = PPApp.Presentations.Open("path to ppt")
'Select the slide
Set PPSlide = PPPres.Slides(3)
set s=ActiveDocument.Sheets("SH07")
ActiveDocument.Sheets("SH07").Activate
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH02").CopyBitmapToClipboard
With PPSlide.Shapes.Paste
.Left = 20
.top = 100
.width=100
.height=120
end with
Set PPSlide = PPPres.Slides(4)
set s=ActiveDocument.Sheets("SH24_093561284")
ActiveDocument.Sheets("SH24_093561284").Activate
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ActiveSheet.CopyBitmapToClipboard
With PPSlide.Shapes.Paste
.Left = 20
.top =80
.width=500
.height=500
end with
Set PPSlide = PPPres.Slides(6)
set s=ActiveDocument.Sheets("SH08")
ActiveDocument.Sheets("SH08").Activate
ActiveDocument.GetApplication.WaitForIdle
Activedocument.fields("Country Project").select "Programme"
ActiveDocument.GetSheetObject("CH04").CopyBitmapToClipboard
PPSlide.Shapes.Paste

Activedocument.fields("Country Project").Clear

ActiveDocument.Sheets("SH25_415204754").Activate

'PoaP France
Set PPSlide = PPPres.Slides(12)

set s=ActiveDocument.Sheets("SH07_337920122")
ActiveDocument.Sheets("SH07_337920122").Activate
ActiveDocument.ActiveSheet.FitZoomToWindow
ActiveDocument.ClearAll False
Activedocument.Fields("Country Project").select "France"
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ActiveSheet.CopyBitmapToClipboard
With PPSlide.Shapes.Paste
.Left = 20
.top =150
.width=400
.height=400
end with
set PPApp = nothing
set PPPRes = nothing
End Sub

Re: Qlikview export to PowerPoint

m_woolf — Thu, 23 Jul 2015 16:08:41 GMT

After pasting the screen into PowerPoint, I added code to crop and scale the image. I found that the user's screen resolution was an issue, so I added code specific to certain resolutions.

Maybe you can use some of this code:

sub SchedIntoPPT(objPPT,objPres,PPSlide,blnIsClient,strScreenRes)

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.ActiveSheet.CopyBitmapToClipboard

PPSlide.Shapes.Paste.Select

Select Case strScreenRes

Case "1680x1050"

dblHeight = 479.5 ' before scaling

dblWidth = 753.89

dblScale = .82

Case "1600x900"

dblHeight = 479.5 ' before scaling

dblWidth = 753.89

dblScale = .82

Case "1440x900"

dblHeight = 476.83 ' before scaling

dblWidth = 757

dblScale = .82

Case else 'if resolution is not handled above, use 1600x900

dblHeight = 479.5 ' before scaling

dblWidth = 753.89

dblScale = .82

end select

dblCropRight = objPPT.ActiveWindow.Selection.ShapeRange.Width - dblWidth

objPPT.ActiveWindow.Selection.ShapeRange.PictureFormat.CropRight = dblCropRight

dblCropBottom = objPPT.ActiveWindow.Selection.ShapeRange.Height - dblHeight

objPPT.ActiveWindow.Selection.ShapeRange.PictureFormat.CropBottom = dblCropBottom

objPPT.ActiveWindow.Selection.ShapeRange.Top = 20

objPPT.ActiveWindow.Selection.ShapeRange.Left = 5

objPPT.ActiveWindow.Selection.ShapeRange.Width = objPPT.ActiveWindow.Selection.ShapeRange.Width * dblScale

end sub

Re: Qlikview export to PowerPoint

Fri, 24 Jul 2015 11:28:10 GMT

Hello

Thank you for your answer. It is definitely doing something I am just not sure how to manipulate those parameters.

As no matter which of those I will use the image is lot larger than a slide...

Do you have any ideas why?

Re: Qlikview export to PowerPoint

m_woolf — Fri, 24 Jul 2015 11:48:32 GMT

Because the screen is larger than a slide.

When the code multiplies the current picture width by the dblWidth variable, it is reducing the width by .82. The height is reduced proportionally.

Re: Qlikview export to PowerPoint

Fri, 24 Jul 2015 12:15:06 GMT

Ok - but it means that if I change a scale to for example .50 it should reduce the image ?

And this is not happening..no matter what I set as a scale - this is what confuses me

Re: Qlikview export to PowerPoint

m_woolf — Fri, 24 Jul 2015 12:17:15 GMT

Please post your current code.

Re: Qlikview export to PowerPoint

Fri, 24 Jul 2015 12:37:07 GMT

Sub ExportPPT

Set PPApp = CreateObject("Powerpoint.Application")

PPApp.Visible = True
Set PPPres = PPApp.Presentations.Open("path")

'Select the slide
Set PPSlide = PPPres.Slides(3)
set s=ActiveDocument.Sheets("SH07")
ActiveDocument.Sheets("SH07").Activate
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH02").CopyBitmapToClipboard
With PPSlide.Shapes.Paste
.Left = 20
.top = 100
.width=100
.height=120
end with
Set PPSlide = PPPres.Slides(4)
set s=ActiveDocument.Sheets("SH24_093561284")
ActiveDocument.Sheets("SH24_093561284").Activate
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ActiveSheet.CopyBitmapToClipboard
PPSlide.Shapes.Paste.Select
set dblCropRight = objPPT.ActiveWindow.Selection.ShapeRange.Width - 753.89
objPPT.ActiveWindow.Selection.ShapeRange.PictureFormat.CropRight = dblCropRight
set dblCropBottom = objPPT.ActiveWindow.Selection.ShapeRange.Height - 479.5
objPPT.ActiveWindow.Selection.ShapeRange.PictureFormat.CropBottom = dblCropBottom
objPPT.ActiveWindow.Selection.ShapeRange.Top = 20
objPPT.ActiveWindow.Selection.ShapeRange.Left = 5
objPPT.ActiveWindow.Selection.ShapeRange.Width = objPPT.ActiveWindow.Selection.ShapeRange.Width * 0.82

set PPApp = nothing
set PPPRes = nothing
End Sub

Re: Qlikview export to PowerPoint

m_woolf — Fri, 24 Jul 2015 12:46:28 GMT

You haven't defined the objPPT object.

You are calling your PowerPoint application object PPApp.

Try replacing objPPT with PPApp.

I'm not sure of your use of set with dblCropRight and dblCropBottom. These are variables that hold a numeric value. Set is used to create an object.

Re: Qlikview export to PowerPoint

Fri, 24 Jul 2015 13:04:48 GMT

Aneta Czorny 24-Jul-2015 09:03 (in response to Aneta Czorny)

Oh that was stupid mistake.

Now the code is working - in the sense that I have the smaller image when I reduce the scale to 0.50 - but it appears to be on top of the larger one - do you know what might cause it?

My code:

Sub ExportPPT

Set PPApp = CreateObject("Powerpoint.Application")

PPApp.Visible = True
Set PPPres = PPApp.Presentations.Open("path")

'Select the slide
Set PPSlide = PPPres.Slides(3)
set s=ActiveDocument.Sheets("SH07")
ActiveDocument.Sheets("SH07").Activate
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH02").CopyBitmapToClipboard
With PPSlide.Shapes.Paste
.Left = 20
.top = 100
.width=100
.height=120
end with
Set PPSlide = PPPres.Slides(4)
set s=ActiveDocument.Sheets("SH24_093561284")
ActiveDocument.Sheets("SH24_093561284").Activate
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ActiveSheet.CopyBitmapToClipboard
PPSlide.Shapes.Paste.Select

PPApp.ActiveWindow.Selection.ShapeRange.PictureFormat.CropRight = PPApp.ActiveWindow.Selection.ShapeRange.Width - 753.89

PPApp.ActiveWindow.Selection.ShapeRange.PictureFormat.CropBottom = PPApp.ActiveWindow.Selection.ShapeRange.Height - 479.5
PPApp.ActiveWindow.Selection.ShapeRange.Top = 20
PPApp.ActiveWindow.Selection.ShapeRange.Left = 5
PPApp.ActiveWindow.Selection.ShapeRange.Width = PPApp.ActiveWindow.Selection.ShapeRange.Width * 0.50

set PPApp = nothing
set PPPRes = nothing
End Sub

Re: Qlikview export to PowerPoint

Fri, 24 Jul 2015 14:29:55 GMT

Oh that was stupid mistake.

Now the code is working - in the sense that I have the smaller image when I reduce the scale to 0.50 - but it appears to be on top of the larger one - do you know what might cause it?

Re: Qlikview export to PowerPoint

m_woolf — Fri, 24 Jul 2015 14:44:12 GMT

I don't see anything in the code that would duplicate the image. Have you accidentally saved the PowerPoint with the large image on the slide?

Re: Qlikview export to PowerPoint

Fri, 24 Jul 2015 15:14:39 GMT

Well yes - you are right. but then I did remove it, saved ppt again and use the code you suggested and it still will paste huge document...don't understand that really...any idea?

ActiveDocument.ActiveSheet.CopyBitmapToClipboard
PPSlide.Shapes.Paste.Select

PPApp.ActiveWindow.Selection.ShapeRange.PictureFormat.CropRight = PPApp.ActiveWindow.Selection.ShapeRange.Width - 753.89
PPApp.ActiveWindow.Selection.ShapeRange.PictureFormat.CropBottom = PPApp.ActiveWindow.Selection.ShapeRange.Height - 479.5
PPApp.ActiveWindow.Selection.ShapeRange.Top = 120
PPApp.ActiveWindow.Selection.ShapeRange.Left = 5
PPApp.ActiveWindow.Selection.ShapeRange.Width = PPApp.ActiveWindow.Selection.ShapeRange.Width * 0.50

Re: Qlikview export to PowerPoint

Fri, 24 Jul 2015 15:22:39 GMT

It looks like

PPSlide.Shapes.Paste.Select

is not working, as after executing the script editor is opened with the cursor in that line...

(sorry if that is stupid assumption, I am quite new to this)