Qlikview export to PowerPoint

[#1]

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

[m_woolf]

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

[#3]

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?

[m_woolf]

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.

[#5]

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

[m_woolf]

Please post your current code.

[#7]

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

[m_woolf]

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.

[#9]

                                               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

[#10]

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?