topic Re: Loop through all filter values then print to PDF in QlikView

Loop through all filter values then print to PDF

pkelly — Mon, 06 Jun 2011 14:06:21 GMT

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.

Loop through all filter values then print to PDF

Mon, 06 Jun 2011 16:25:32 GMT

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.

Re: Loop through all filter values then print to PDF

christian77 — Mon, 06 Jun 2011 16:36:10 GMT

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.

Loop through all filter values then print to PDF

pkelly — Tue, 07 Jun 2011 07:48:46 GMT

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.

Re: Loop through all filter values then print to PDF

christian77 — Tue, 07 Jun 2011 08:09:04 GMT

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

This macro has to be complete already in the community. To print and to send. In different ways. Look around.

Loop through all filter values then print to PDF

christian77 — Tue, 07 Jun 2011 08:35:33 GMT

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

Re: Loop through all filter values then print to PDF

christian77 — Tue, 07 Jun 2011 08:40:30 GMT

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.

Re: Loop through all filter values then print to PDF

pkelly — Tue, 07 Jun 2011 09:16:32 GMT

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

ActiveDocument.ClearAll true

MsgBox ("Emails have been sent")

end sub

Re: Re: Loop through all filter values then print to PDF

Tue, 08 Jul 2014 12:17:07 GMT

Hi Paul,

I tried your macro ... but it is not working .I want to iterate through all the values in CallID !

PFA my daashboard. I want to make pdf of first sheet for all values of listbox LB1 in Sheet3.

Thanks,

Anju

Re: Loop through all filter values then print to PDF

Wed, 02 Sep 2015 07:57:35 GMT

Hi Paul,

I have a similar requirement like yours. So can you please upload your full macro on the forum.

Re: Loop through all filter values then print to PDF

pkelly — Wed, 09 Sep 2015 15:20:01 GMT

Apologies for the delay - have been on holiday...

The attached qvw shows how I managed to get it to loop through the values.

Points to note...

I am using "PDF-XChange 3.0" as my PDF printer.

I have a VBScript file which first of all checks to see if QlikView is running.

The reason for this is that, I have various reports running from a batch file and found that if I didn't wait until the previous report had completed, I could end up e-mailing the wrong PDF - then opens the qvw and clicks BU80a which starts the macro running.

================================================================

Dim objQV
Dim boolLoopAgain

boolLoopAgain = False

Do
On Error Resume Next

boolLoopAgain = False

' Try to grab a running instance of QlikView...

Set objQV = GetObject(, "QlikTech.QlikView")

' MsgBox objQV

If TypeName(objQV) = "Global" Then

' QlikView is Running

boolLoopAgain = True

Else

Set MyApp = CreateObject("QlikTech.QlikView")
Set MyDoc = MyApp.OpenDoc ("C:\QlikViewV13\Apps\PR_Performance\PR07.qvw","","")
Set ActiveDocument = MyDoc
ActiveDocument.Reload
ActiveDocument.GetSheetObject("BU80a").Press

End If

Loop While boolLoopAgain

========================================================================

Hope this helps...

Regards

Paul

Re: Loop through all filter values then print to PDF

Andrius45 — Wed, 30 Nov 2016 09:05:42 GMT

Hey Paul,

First of all big thanks for this amazing macro and even an example file. I am trying to implement it right now and do everything step by step by your example. Are you still using the same macro or have you tweaked it since?