Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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
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.
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
Hi Frank,
Please find attachment..
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
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
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?
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,
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!