Monday 13 February 2012

Printing Reports to PDF using PDFCreator

PDF Creator is a nice tool for generating PDFs and it comes at a very attractive price.  Like other tools of this ilk, it creates a Printer driver which you can print your documents to.  Unlike other drivers (especially at this price point), it gives you additional control on what is printed using a COM interface.  This makes is quite handy to use from VBScript - either inside or outside QlikView.

I am going to give you some starter code here on how to do this from within a QlikView document using a Macro.  You can also do this from a .vbs file that is external to QlikView.  Just note that if you are using a .vbs then you will still need an appropriately licensed copy of QlikView Desktop to run this.

*** All script here is completely unsupported by myself or anyone at QlikTech.  Use at your own risk ***

If you are going to use a .vbs then you will need to create your own ActiveDocument variable.  This is how I do that:


    Dim QV, ActiveDocument


    set Qv = CreateObject("QlikTech.QlikView")
    

    QV.OpenDoc Document,"",""

    set ActiveDocument = Qv.ActiveDocument


In this case, the variable Document contains the full path to my .qvw.  The 2nd and 3rd parameter are a QVUser and Password - if you have those in Section Access.  Once you have an ActiveDocument object, the code is the same between the Macro and the .vbs.

Usually when I do this, I am going to loop across all the value in a field.  For example, I may have a field called "Seg" and I want to run a report for each value in this field.

I may choose to clear all values first:




    
ActiveDocument.ClearAll


Either way, I can call the GetPossibleValues to get a list of all the values in that field:



    Dim FieldName
    
    FieldName = "Seg"


    set mySelections = ActiveDocument.Fields(FieldName).GetPossibleValues


Now, I can loop through the values, select each one in the document and call a function that I have made called Print_PDF for each value:


    Dim i


    for i = 0 to mySelections.Count - 1


        Dim FieldValue


        FieldValue = mySelections.Item(i).text
        
        ActiveDocument.Fields(FieldName).Select FieldValue


        Print_PDF FieldValue, "My Report", "RP01"
        
    Next


Print_PDF is, essentially, using the reference code from the PDF Creator documentation:


Sub Print_PDF(FieldValue, ReportName, ReportID)


    ' Designed for early bind, set reference to PDFCreator
    Dim pdfjob
    Dim sPDFName
    Dim sPDFPath


    '/// Change the output file name here! ///
    sPDFName = ReportName & " - " & FieldValue
    sPDFPath = "C:\PDFReports"


    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")


    With pdfjob


      If .cStart("/NoProcessingAtStartup") = False Then
         If .cStart("/NoProcessingAtStartup", True) = False Then

          Exit Sub

         End if
         .cVisible = True
      End If


      .cOption("UseAutosave") = 1
      .cOption("UseAutosaveDirectory") = 1
      .cOption("AutosaveDirectory") = sPDFPath
      .cOption("AutosaveFilename") = sPDFName
      .cOption("AutosaveFormat") = 0 ' 0 = PDF
      .cClearCache


    End With



    ' Print the QlikView Report
    ActiveDocument.PrintReport ReportID, "PDFCreator"



    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
     ActiveDocument.GetApplication.Sleep 20
        ' in VBScript use WScript.Sleep(20)
    Loop
    pdfjob.cPrinterStop = False


    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
     ActiveDocument.GetApplication.Sleep 20
        ' in VBScript use WScript.Sleep(20)
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing


End Sub


The only additional piece here from the PDF Creator documentation is the QlikView call to Print the report:


    ActiveDocument.PrintReport ReportID, "PDFCreator"


I pass the field value and a ReportName value into the function so that I can generate a different PDF file name for each field value.

Last thing is that you will need to enable System Access in the Macro settings.  In a way, this is where .vbs has an advantage.  Because the script is already external, there is no issue with enabling System Access.

Enjoy.  Just remember, this is all unsupported so use at your own risk.


Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

15 comments:

  1. I'm getting this error. What should I be looking for?

    ActiveX component can't create object: 'PDFCreator.clsPDFCreator'

    ReplyDelete
  2. Errggg..... I didn't have sytem access turned on. This works great!!

    ReplyDelete
  3. Its works great, but why do I get "Unexpected Error". Quiting at the end. Though the PDF have been created.

    Any idea?

    ReplyDelete
  4. Hi,

    No idea. Something to do with your system, maybe re-install something.

    I wouldn't expect an unexpected error ;-)


    Stephen

    ReplyDelete
  5. Hi Stephen,
    Your macro is fantastic and works perfectly.
    Beyond what you have done, I've one more requisit which is to email each of the reports to different email. Could you help please?
    Many Thanks
    Bruno

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  6. Hi Stephen,

    it is possible to create PDf file with QV8.5 or only with Release 11.0?
    Thanks Kira

    ReplyDelete
  7. Hi, I'm put into practice your code but I have one problem when executing the script:

    It appears small Qlikview window with message 'Failed to open document', it opens an empty Qlikview window and then a typical vbasic error message window where puts: Object required (Line 6). My 6 first lines are:

    Line 1- Dim QV, ActiveDocument, FieldName, i, FieldValue, mySelections
    Line 2 - set QV = CreateObject("QlikTech.QlikView")
    Line 3- QV.OpenDoc "url_to_qvw","",""
    Line 4 - set ActiveDocument = Qv.ActiveDocument
    Line 5 - FieldName = "Client_ID"
    Line 6 - set mySelections = ActiveDocument.Fields(FieldName).GetPossibleValues

    After that I make the loop calling to function Print_PDF. I dont know what can be happening.

    I make tests with Qlikview 11 SR1 64bit and PDF Creator 1.6.2.

    Any idea??

    Thank you!

    ReplyDelete
  8. "url_to_qvw" doesn't look like a valid path.

    ReplyDelete
  9. Obviously I put that ommitting a real path. In my case, it would be "C:\test.qvw" but i dont understand well because it gives me that error :S

    ReplyDelete
  10. i am getting error at


    set mySelections = ActiveDocument.Fields(FieldName).GetPossibleValues


    in the line" set mySelections = "
    here wht should i give

    ReplyDelete
  11. Hello.

    i have a problem with PDFCreator (i use the old version 1.7.3)

    When i run the Nacro from QV, it works very fine. But when i use a bath file to run my qvw file, after closing my app, there is a PDFCreator.exe in my memory and i have to kill it manually.

    Do you have any idea, what can be the reason?

    Many Thank in forward!



    Best regards

    Pey


    (too much deleted comments??!!)

    ReplyDelete
    Replies
    1. Hi, sorry, I don't know what the problem is. It has been some time since I even looked at this - there are better options now.

      Deleted comments are spammers.

      Delete
    2. What are these better options? Can you link to them? I am currently try to find a script that creates multiple pdf files based on a customers name. Would really appreciate the help!

      Delete

Note: only a member of this blog may post a comment.