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

Report Inappropriate Content · ‎2016-10-26

Frank,

Thank you for your response.

Updated the macro, as well as the powerpoint.

click the button to run the macro

macro automatically selected the Year then the BusGrp

it openned all available tabs (sheet) in my project, even the hidden sheet

save one output without a file name but '-'

no other files are saved

has to close qlikview using task manager as macro is still running in the background

i cannot find the attached button in the qlikview community...

regards,

Frank_Hartmann · ‎2016-10-26

for attaching the txt file try as follow:

Did you setup the Qlikvariable (Settings - Variable Overview - Add):

vFinal=GetFieldSelections(BusGrp) &'-'& GetFieldSelections(Year) ) ???

make sure that the vFinal variable contains the fields in wich you want to select and

these fields have to be equal to the fields which you are useing for looping inside the macro:

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=1991 to 2016
    ActiveDocument.Fields("Year").select w

Report Inappropriate Content · ‎2016-10-27

Yes, variables is set as per your instruction. it contains the fields.

I've notice yesterday, that the macro is not working unless ppt is open, meaning,i have to open the exporttemplate.pptx prior to running the macro in qlikview

after clicking the button with the macro, it open all the sheets I have in qlikview including those hidden sheets that I don't use

it save only 1 file without a filename but '-'.

I notice in the lower left corner of the screen stating "macro running" that runs for three hours and counting and I have to terminate using task manager.

I still don't have this facility to attach anything... not sure why, maybe because of the browser, or maybe no rights to upload...

Report Inappropriate Content · ‎2016-10-27

if not asking too much, as I cannot attach here in this thread, please copy this to text file for further analysis.

thanks

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

''*** Export to PPT ***

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

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 "2016"

FOR w= 2014 to 2016

ActiveDocument.Fields("Year").select w

Set PPApp = CreateObject ("Powerpoint.Application")

'PPApp.Presentations.Open "C:\Users\Admin\Desktop\Export\Exporttemplate.pptx" 'Here you specify the path of your TEMPLATE

PPApp.Presentations.Open "D:\Qlik\AdhocReports\Turnover\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

PPApp.ActivePresentation.SaveAs filePath

PPApp.ActivePresentation.Close

'msgbox("Juhuuu")

End Sub

Frank_Hartmann · ‎2016-10-27

For my environment your attached code works fine. I have no clue why its not working on your system.

What happens if you are not executing the macro per button but inside module by pressing the "Test" Button?

Is there any errormessage appearing then?

If you want me to look over your file by teamviewer, let me know!

Report Inappropriate Content · ‎2016-10-27

Edit module mode

I click "Test" macro button

error: Presentations Open: Invalid request. The PowerPoint Frame windows does not exist

Frank_Hartmann · ‎2016-10-27

which Powerpoint Version are u using?

Report Inappropriate Content · ‎2016-10-27

2007,

in the syntax change 'objecte' to 'object'

macro is running...

powerpoint open after clicking the button,

macro is opening all the sheets available in my project

lower left screen 'macro running'

lower left screen ' auto saving'

lower left screen ' macro running'

this runs endlessly and need to close qlikview using task manager

check the file

save only one file without a name but '-'

would you mind putting remarks in each line that may assist me to understand how the syntax run

like, where is the loop, where is the syntax that auto save file with different file name, etc

my apology requesting too many things

regards

Report Inappropriate Content · ‎2016-10-27

I am using windows 10

Frank_Hartmann · ‎2016-10-27

OK, which MS Office Package do you use? Do you have Powerpoint 2003 or Powerpoint 2007 or Powerpoint 2010 ......installed?

dynamically save various reports as pdf with different filename