11 Replies Latest reply: Nov 30, 2016 4:05 AM by Andrius Sarkunelis RSS

    Loop through all filter values then print to PDF

    Paul Kelly

      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
          Neil Miller

          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
              Christian Conejero

              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
                  Paul Kelly

                  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
                      Christian Conejero

                      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

                       

                      next

                       

                      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
                          Christian Conejero

                          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
                    Christian Conejero

                    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
                        Paul Kelly

                        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

                                        Next

                                        ActiveDocument.ClearAll true

                                        MsgBox ("Emails have been sent")

                        end sub

                         

                         

                         

                         

                         

                         

                         

                         

                         

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

                        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
                            Paul Kelly

                            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