topic PPT parameters in QlikView

PPT parameters

Thu, 10 Sep 2015 10:47:10 GMT

Hey,

I am creating an export from qlikview to PowerPoint.

One of those ppt will be having over 40 slides, each of them will be exactly the same just filtered by different country.

Is there any way to write a Sub and use a country name as parameter and then just call that sub ?

the trouble I have is that if I have following Sub:

sub CreateCountry(slide_nr, CountryName)

Set PPSlide = PPPres.Slides(n)
set s=ActiveDocument.Sheets("SH11")
ActiveDocument.Sheets("SH11").Activate
ActiveDocument.GetApplication.WaitForIdle
Activedocument.fields("Country Project").select "CountryName"
ActiveDocument.GetSheetObject("CH09").CopyBitmapToClipboard
PPSlide.Shapes.Paste
call Fix_Picture_Size(0.49,0.1,0.4,0.2,PPSlide.Shapes(PPSlide.Shapes.Count),PPSlide.Master.Width)
Activedocument.fields("Country Name").Clear

End Sub

And then I call this Sub:

call CreateCountry(1, "United Kingdom")

It doesn't work...

Any ideas what I got wrong?

Thanks

Re: PPT parameters

marcus_sommer — Thu, 10 Sep 2015 10:50:28 GMT

Have a look on this: Automated PowerPoint Presentation Extraction.

- Marcus

Re: PPT parameters

Thu, 10 Sep 2015 13:16:29 GMT

Hey,

Yeah it does the job, but this is not exactly what I ment.

I just want to know how to reference to the parameter of the function in

Activedocument.fields("Country Project").select "CountryName"

so it will actually do the filtering.

Any ideas?

Re: PPT parameters

marcus_sommer — Thu, 10 Sep 2015 13:27:04 GMT

In this case it should look more like:

sub CreateCountry(slide_nr, CountryName)

Set PPSlide = PPPres.Slides(slide_nr)
set s=ActiveDocument.Sheets("SH11")
ActiveDocument.Sheets("SH11").Activate
ActiveDocument.GetApplication.WaitForIdle
Activedocument.fields("Country Project").select CountryName
ActiveDocument.GetSheetObject("CH09").CopyBitmapToClipboard
PPSlide.Shapes.Paste
call Fix_Picture_Size(0.49,0.1,0.4,0.2,PPSlide.Shapes(PPSlide.Shapes.Count),PPSlide.Master.Width)
Activedocument.fields("Country Name").Clear

End Sub

- Marcus

Re: PPT parameters

Thu, 10 Sep 2015 13:34:55 GMT

Ok, Thanks.

And how do I call that sub in the code for United Kingdom?

Re: PPT parameters

marcus_sommer — Thu, 10 Sep 2015 13:37:44 GMT

In this way like you have done it:

call CreateCountry(1, "United Kingdom")

Re: PPT parameters

Thu, 10 Sep 2015 13:42:47 GMT

I thought so, but it doesn't work..

So here is my Sub:

Sub CreateCountry(slide_nr, CountryName)
'****New York CB&S
'Rag status

Set PPSlide = PPPres.Slides(slide_nr)
set s=ActiveDocument.Sheets("SH11")
ActiveDocument.Sheets("SH11").Activate
ActiveDocument.GetApplication.WaitForIdle
Activedocument.fields("Country Project").select CountryName
ActiveDocument.GetSheetObject("CH09").CopyBitmapToClipboard
PPSlide.Shapes.Paste
call Fix_Picture_Size(0.49,0.1,0.4,0.2,PPSlide.Shapes(PPSlide.Shapes.Count),PPSlide.Master.Width)
Activedocument.fields("Country Name").Clear

End Sub

Here is how I call it:

call CreateCountry(1,"New York CB&S")

it is just opening the Macro Window and does nothing.

Do you know whats wrong?

Re: PPT parameters

marcus_sommer — Thu, 10 Sep 2015 13:55:31 GMT

Yes, if this is the complete code then there are some missing things like createobject() - this meant you need at first create a powerpoint-instance. For this have a look on the link from my first answer.

- Marcus

Re: PPT parameters

Thu, 10 Sep 2015 13:57:52 GMT

no it is not a complete code, I have the bit to open powerpoint and if I do the same without Sub it works perfectly.

So the problem needs to be in the way how I call the parameters?

Maybe with quotation signs?

But I already tried different configuration and nothing works.

Re: PPT parameters

marcus_sommer — Thu, 10 Sep 2015 14:12:03 GMT

Maybe you tried it in this way:

slide_nr = 1

CountryName = "New York CB&S"

call CreateCountry(slide_nr, CountryName)

and used instead a sub-routine a public function. I have excactly such callings which work fine.

- Marcus

Re: PPT parameters

Thu, 10 Sep 2015 14:28:19 GMT

Thank you for your response.

Did as you advice:

public function CreateCountry(slide_nr, CountryName)

...

End function

and called with variables, as you suggested and still the same...

Not sure why it is doing it - do you have any other suggestions?

Re: PPT parameters

marcus_sommer — Thu, 10 Sep 2015 14:32:51 GMT

I think the reason aren't the call or the parameters - something else caused this error. I suggest you put some msgbox into the script to find out until which point the routine(s) runs.

- Marcus