Solved: Loop through all filter values then print to PDF - Qlik Community

pkelly · ‎2011-06-06

Hi all...

I have a list box with about 30 entries in it (these could change).

For each entry, I want to...

1) Select it.

2) Run a macro that will create a PDF and e-mail the file.

I have already written a report which creates a PDF then e-mails the file so that part is covered.

What I don't know how to do is interrogate my list box then apply a filter, then move on to the next one until I have worked my way through them all.

Hope that makes sense...

Any help greatly appreciated.

Regards

Paul

PS - looping over possible values in field won't help me as I need an individual PDF for each selection.

pkelly · ‎2011-06-07

What I used was...

sub SubmitReport

Set val=ActiveDocument.Fields("csp_Owner").GetPossibleValues(20000)

For i = 1 to Val.count - 1

ActiveDocument.Fields("csp_Owner").Select val.item(i).Text

' append csp_Owner to end of emailSubject variable

set v = ActiveDocument.Variables("emailSubject")

set vname = ActiveDocument.Fields("csp_Owner").GetPossibleValues

emailSubject = "QlikView TS10 Report - " + vname.Item(0).text

v.SetContent emailSubject, true

' Print Report

printReportPDF "RP01", "C:\QlikViewFiles\Timbmet_PDF\TS10.pdf"

' Email Report

SendMail

MsgBox ("Emails have been sent")

end sub

View solution in original post

Report Inappropriate Content · ‎2011-06-06

You can make Selections using a macro. To make the selections using VB, you will need an array containing all of the values you want to select. There are multiple ways to do this, but for me, I would use the QlikView function Concat(FIELD, ','). You could do: list = ActiveDocument.Evaluate("Concat(FIELD, ',')"). Then use the VB function Split to get that list into an array.

Once you have the values in an array, use a For...Next loop to loop through all of the values. In the loop, Select the value in the appropriate array position, then run your PDF macro based on the current selections. As you go through the loop, the next Select will clear out the previous and you should get a PDF for each selection.

If you need more info on a specific piece, it may be easier to post what you have so far and somebody should be able to fill in the blanks.

christian77 · ‎2011-06-06

Hi:

You don´t really need any macro.

Get a pdf printer like pdf x-change provided free from QV downloads only for QV or other. Install it. Set it as default.

Make you report.

Now with your report in edition mode do:

Page > Page Settings > check the option Loop page over possible values of field. Then select the field from the combo.

Voila.

Add a cover page. In the save options choose add to last report.

You´ll get a whole booklet with your data, paged by values of the field.

If you choose not to add, you´ll have a two page report per each value of the field.

Also you can make a macro to do it automatically and even send it as an atachment, each one to its destiny.

Remember to unselect. It does it with possible values.

Ok.

pkelly · ‎2011-06-07

Thanks for the response Christian...

As I said in my post, I am aware of the loop option but cannot use this as this gives me one complete document - I need to create an individual PDF file for each selection - these will eventually be e-mailed out to our business managers.

christian77 · ‎2011-06-07

You can manage that changing the configuration of your printer.

But,... if you want to do it in a macro.

go like:

Unselect Everything

set var = ActiveDoc.Field(yourfield).GetPossibleValues -GetPossibleValues(2000) will take 2000

for i = 0 to var.getCardinal -1

do it and blah, blah

christian77 · ‎2011-06-07

Something like this.

I didn´t use GetPossibleValues cause I thought it took only 100, but GetpossibleValues(200) will take 200 and so on.

sub ImprimirPDF
set rutainformes = ActiveDocument.Variables("vRutaInformes")
set mvUsuario = ActiveDocument.Variables("vUsuario")
'msgbox(mvUsuario.GetContent.String)

Dim response2
response2 = MsgBox ("Se dispone a enviar correo masivo. ¿Está segur@?",49, "Alerta 2ª")
if response2 =vbCancel then
exit sub
else
end if

ActiveDocument.Fields("Franquicia_id Origen2").Select ""
set Valores=ActiveDocument.Fields("Franquicia_id Origen2")
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.Deletefile (rutainformes.GetContent.String & "*")

for i=0 to Valores.GetCardinal-1 (try first with –n)
     ActiveDocument.Fields("Franquicia_id Origen2").Select (i+1)
     set val7=ActiveDocument.Fields("Franquicia Origen").GetPossibleValues
     set WSHShell = CreateObject("WScript.Shell")
     pdfPathName = rutainformes.GetContent.String & "QlikView Printing.pdf"
    ActiveDocument.PrintReport "RP01", pdfPathName, false
    'msgbox (rutainformes.GetContent.String)
    ActiveDocument.GetApplication.WaitForIdle
     ActiveDocument.GetApplication.Sleep 1000

     objFSO.MoveFile rutainformes.GetContent.String & "QlikView Printing.pdf" , rutainformes.GetContent.String & (val7.Item(0).Text) & mvUsuario.GetContent.String & ".pdf"
    set val5=ActiveDocument.Fields("Email Franquicia Origen").GetPossibleValues
    ''discrimina e-mails nulos
     if trim(val5.item(0).text)<>"" then
           mandar (rutainformes.GetContent.String & val7.Item(0).Text & mvUsuario.GetContent.String & ".pdf"), val5.Item(0).Text

else
end if
next
ActiveDocument.Fields("Franquicia_id Origen2").Select ""
ActiveDocument.Fields("Franquicia Origen").Select ""
end sub

christian77 · ‎2011-06-07

And then.

function mandar (InformeAdjunto, Email)
     Dim objEmail

     Const cdoSendUsingPort = 2

     Const cdoAnonymous = 0
     Const cdoBasic = 1
     Const cdoNTLM = 2

     SMTPServer = "mail.server.com"

     Const SMTPPort = 25
     Const SMTPTimeout = 60

     'Sending mail
     strMailattach = InformeAdjunto
     Set objEmail = CreateObject("CDO.Message")
     Set objConf = objEmail.Configuration
     Set objFlds = objConf.Fields
     With objFlds
           '---------------------------------------------------------------------
          ' SMTP server details
          .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
          .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
          .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoAnonymous
           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort
           .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
          .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTPTimeout
          .Update
           '---------------------------------------------------------------------
     End With

     objEmail.To =    Email
     objEmail.From = any@server.com

     objEmail.Subject = "Os enviamos el informe mensual de franquicia"
     objEmail.TextBody = "Os enviamos el informe mensual de franquicia"
     'msgbox(strMailattach)
     objEmail.AddAttachment strMailattach

     objEmail.Send

     Set objFlds = Nothing
     Set objConf = Nothing
     Set objEmail = Nothing

     msgbox ("Mensaje enviado correctamente a" & Email & " " & strMailattach)    ''comment later
end function

Watch to requirements. Port 25 must be open, printer installed and as default printer, etc.

pkelly · ‎2011-06-07