Solved: dynamically save various reports as pdf with diffe... - Page 3 - Qlik Community

Report Inappropriate Content · ‎2016-09-29

Dear All,

I am trying to automatically save various reports as pdf. Reports should have different file name. May i kindly ask how to do it in qlikview.

I appreciate your help.

Regards,

Frank_Hartmann · ‎2016-10-11

this is the part where the macro saves a report when one loop is finished:

set v=ActiveDocument.Variables("vFinal")

getVariable = v.GetContent.String

filePath = "C:\Users\Admin\Desktop\Export\generated_Reports\"&getVariable&".pptx"

PPApp.ActivePresentation.SaveAs filePath

PPApp.ActivePresentation.Close

this is why you need to create a QV-Variable called:

vFinal=GetFieldSelections(company) &'-'& GetFieldSelections(year)

the vFinal variable returns the active selection in the fields company and year

and the macro uses these selections to write them into the filename after every loop!

hope this helps

Report Inappropriate Content · ‎2016-10-12

Hi Frank,

Thanks.

It gives me error, please find below my remarks, questions.

Sub Export

     FOR t=1 to 3

     ActiveDocument.Fields("Business
Group").select t ‘Business group is the field to select

     ActiveDocument.Fields("Year").select
"2016"

     FOR w= 2016 to 2016     ‘ I don’t need this

     ActiveDocument.Fields("Year").select
w ‘ I don’t need this

     Set PPApp = CreateObject("Powerpoint.Application")

     PPApp.Presentations.Open
"D:\Qlik\Adhoc Reports\Executive\ExcRep.pptx"          'Here you specify the path of your TEMPLATE

     PPApp.Visible = True

     Call PPApp.Activate

     For i = 0 To
ActiveDocument.NoOfSheets - 1

          Set ss=
ActiveDocument.GetSheet(i) ‘ do I need to put qlikview sheet name here?

          shNa =
ss.GetProperties.Name


          ActiveDocument.ActivateSheet(shNa)

          ActiveDocument.GetApplication.WaitForIdle()                                      'Please activate if you loop through more than one
sheet to get your objects exported

          Objecte =
ActiveDocument.ActiveSheet.GetSheetObjects



          For j = LBound(Objecte) To
UBound(Objecte)

              If Objecte(j).GetObjectType
= 12 Or Objecte(j).GetObjectType = 15 Or Objecte(j).GetObjectType = 16 Or
Objecte(j).GetObjectType = 11 Or Objecte(j).GetObjectType = 6 Or
Objecte(j).GetObjectType = 22 Then

                   NameObject =
Objecte(j).GetObjectID

                   z=z+1

                   If
InStr(NameObject,"Hintergrund")=0
Then

                        Objecte(j).CopyBitmapToClipboard

                        v = 0

                        For Each s1 In
PPApp.ActivePresentation.Slides

                             v = v + 1

                        Next

                        For u = 1 To
v

                             For Each Shp
In PPApp.ActivePresentation.Slides(u).Shapes


                                  If
Shp.Name = NameObject Then

                                      ChTop
= Shp.Top

                                       ChLeft = Shp.Left

                                      ChWidth
= Shp.Width

                                      ChHeight
= Shp.Height


                                      Shp.Delete


                                       PPApp.ActivePresentation.Slides(u).Select

                                      With
PPApp.ActivePresentation.Slides(u).Shapes.PasteSpecial(1)

                                           r=r+1

                                           .Top
= ChTop

                                           .Left
= ChLeft

                                            .Width
= ChWidth

                                           .Height
= ChHeight

                                           .name
= NameObject

                                           .Line.Weight
= 2

                                           .Line.Transparency
= 0

                                           .Line.Visible
= msoTrue

                                           .Line.Forecolor.RGB
= RGB(100,100,100)

                                           .Line.Backcolor.RGB
= RGB(200,200,200)

                                           .ZOrder
1

                                           With
.PictureFormat

                                                .TransparentBackground
= True

                                                .TransparencyColor
= RGB(255,255,255)

                                           End
With

                                      End
With


                                  Exit
For

                                    End If

                             Next

                        Next

                   End If

              End If





     Next



     Next



     'vFinal=GetFieldSelections(Business
Group) ‘ do I need to define it here?
     set
v=ActiveDocument.Variables("vFinal")

getVariable = v.GetContent.String

filePath = "D:\Qlik\Adhoc
Reports\"&getVariable&".pptx" 'Please
adjust according to your local environment

PPApp.ActivePresentation.SaveAs filePath

PPApp.ActivePresentation.Close

     Next



     Next

msgbox("Juhuuu")

End Sub

Frank_Hartmann · ‎2016-10-12

can u please upload your qvw with scrambled data (settings --> document properties --> scrambling --> select the fields you want to scramble and then save the document) and your prepared template.

that would make it much easier to assist.

Report Inappropriate Content · ‎2016-10-13

Hi Frank,

Thanks. I will send you the files as soon as i figure out how to. there is no button to click in this thread

Report Inappropriate Content · ‎2016-10-13

Hi Frank,

Please find attachment..

Frank_Hartmann · ‎2016-10-13

For which selections should the reports be generated?

Selections in Year and BusGrp? Or any further selections?

And which years do you want to show? From 1991-2016?

If yes, then try attached macro. You will only have to adapt the two paths inside the macro (Template and Exportfolder). Keep the rest as it is. Then put the attached Powerpoint-Template into the correct Folder.

After that go to settings --> variable overview -->add--> vFinal=GetFieldSelections(BusGrp) &'-'& GetFieldSelections(Year)

(make sure that you keep the "=" equal sing in the "Definition" field)

Finally make an exportbutton and run the macro! For me this worked fine :-).

Hope this helps

Report Inappropriate Content · ‎2016-10-24

Hi Frank,

Tried the macro as well as the pptx, still giving me an error.

In one of the line (in Macro) it says

If InStr(NameObject, "Hintergrund") = 0 Then

May i kindly ask what is "Hintergrund"

Maybe this is the reason why i am having an error.

I appreciate our help.

Regards,

Obet

Frank_Hartmann · ‎2016-10-24

i don't think so. Even with this line active the macro should work.

If InStr(NameObject, "Hintergrund") = 0 Then

means, that if the word "Hintergrund" is not part of your object ID then start exporting.

I used this macro to export several charts to a powerpoint doc overlaying each other.

So my macro had an extra loop to bring the Charts to the correct layer!

Actually you could delete the line and it should still work (but also delete End If).

May I ask for the errormessage you received in module?

Did you make sure that you have setup the correct security settings?

Report Inappropriate Content · ‎2016-10-25

Dear Frank,

Thanks for your usual support.

security settings is as per your post, I am using windows 10

the macro selected "busgrp" and "year" then open the module (in qlikview) no error messages. i have noticed that after selecting busgrp and year, the macro is not opening the template, though path is correct.

regards,

Frank_Hartmann · ‎2016-10-25

Ok, so did you setup the powerpoint template correctly?

You will have to copy the chart/charts from Qlikview to Powerpoint (copy - paste).

If your Qliview Object-ID is e.g. "CH01", then you will have to name the Powerpoint Object "Document\CH01"

Then the macro should find the connection to your powerpoint objects.

P.S.: Do you use the field "busgrp" ("year)" or "BusGrp" ("Year")???

If your Field is "busgrp" ("year"), then you will have to adapt this name in macro.

If your field is "BusGrp" ("Year") then the macro is setup correctly.

And please attach the macro code you are using as txt-file!

dynamically save various reports as pdf with different filename