Solved: dynamically save various reports as pdf with diffe... - Page 5 - 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-27

Sorry didnt see the above post

Frank_Hartmann · ‎2016-10-27

do not change objecte to object!!

Is it possible that you are not allowing to export? (Settings--> Document Properties--> Security)

Report Inappropriate Content · ‎2016-10-27

allow export checked

run the macro

still opening all sheets (even the unused sheets)

year is selected,

2014, 2015, 2016, no year selected

looping continuously

Report Inappropriate Content · ‎2016-10-27

i'll try tonight to run using the one sheet file i have sent you, maybe i could isolate what i was doing wrong..

i'll let you know or i'll keep on asking for your help until this matter is close

thank you for your kind help, indeed

Frank_Hartmann · ‎2016-10-27

Give it one more try:

''*************************************************************************************

''*** Export ***

''*************************************************************************************

Public Sub Export

SET Doc = ActiveDocument

SET Field = Doc.Fields("BusGrp").GetPossibleValues

FieldCount = Field.Count

FOR p=0 to Field.Count -1

Doc.Fields("BusGrp").SELECT Field.Item(p).Text

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

For w= 2014 to 2016

ActiveDocument.Fields("Year").select w

Set PPApp = CreateObject("Powerpoint.Application")

PPApp.Presentations.Open "D:\Qlik\AdhocReports\Turnover\Exporttemplate.pptx" 'Here you specify the path of your TEMPLATE

'PPApp.Presentations.Open "C:\Users\Admin\Desktop\Export\Exporttemplate.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)

shNa = ss.GetProperties.Name

ActiveDocument.ActivateSheet(shNa)

ActiveDocument.GetApplication.WaitForIdle()

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 Or Objecte(j).GetObjectType = 13 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

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

Exit For

End If

set v=ActiveDocument.Variables("vFinal")

getVariable = v.GetContent.String

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

'filePath = "C:\Users\Admin\Desktop\Export\generated_Reports\"&getVariable&".pptx" 'Please adjust according to your local environment

PPApp.ActivePresentation.SaveAs filePath

PPApp.ActivePresentation.Close

msgbox("Juhuuu")

End Sub

I tried this code with your "Human Capital Analytics.qvw" and its working fine for me. Just copy/paste and run. Aslong as you placed the template to the correct folder and as long as the path to the generated Reports folder exists it should open the template and loops through the selected fields!

Report Inappropriate Content · ‎2016-10-30

Frank, \

I've tried it to the same sample qlikview file i have sent you and its working. though it doesn't select other BusGrp. File save as only one BusGrp for three different years.

My question is why if there are more that one sheet, the macro reads and open all the sheet

Regards,

Frank_Hartmann · ‎2016-10-30

because the macro loops through all your sheets in order to search for Qlikview objectIDs which are linked to the Powerpointdocument

(with Document\ObejctID as link). That means if you have all your charts, which should be exported,

on one sheet then you dont need the loop. But if the charts are placed over more than one QV-sheets then you will need this loop.

If you have all your charts on one QV Sheet then I see two possible solutions for this, depending on your requirement:

First, you could create a variable and set the visibility of all the sheets to the condition of this variable. then you could add a line at the beginning of the macro where you set this variable to 0 for all sheets except the one from which you want to export the charts. This solution would be perfect if you want to export the charts to more than one Powerpoint slide!

Second, you could delete the loop for selecting all the sheets and modify your macro like attached txt file . This will export all 4 charts to one PP slide you will just have to adapt the sheetID and ChartIDs and the positioning of the charts!

hope this helps

Report Inappropriate Content · ‎2016-10-30

Hi Frank,

I think this best fits my requirements:

"Second, you could delete the loop for selecting all the sheets and modify your macro like attached txt file . This will export all 4 charts to one PP slide you will just have to adapt the sheetID and ChartIDs and the positioning of the charts"

Please send the updated macro with this option.

Thanks

Frank_Hartmann · ‎2016-10-30

See attached txt file!

you will just have to adapt the sheetID and ChartIDs and the positioning of the charts

Report Inappropriate Content · ‎2016-10-30

Hi Frank,

Cannot see the attachment.

dynamically save various reports as pdf with different filename