Loop through all filter values then print to PDF

[pkelly]

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.

[#2]

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]

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]

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]

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.

[christian77]

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]

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 · Accepted solution]

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

[#9]

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

[#10]

Hi Paul,

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