topic Re: Export to PDF Macro is not working as expected in QlikView

Export to PDF Macro is not working as expected

nirmalraj_kj — Mon, 23 Nov 2015 08:18:30 GMT

Hi Friends,

I have been working on requirement for last couple of days. I just want to brainstorm on the below scena

I want to print report as PDF and save it in a particular path. I have used the below macro.

Sub Auto_Macro

'ActiveDocument.clearall

ActiveDocument.PrintDocReport ("RP01"),"QlikViewPDF", false

set rep = ActiveDocument.GetReport("RP01")

reportFile = "C:\Nirmal\Test"&".pdf"

printReportPDF(reportFile)

ActiveDocument.GetApplication.Sleep 12500

end sub

Function printReportPDF(pdfOutputFile)

Set WSHShell = CreateObject("WScript.Shell")

WSHShell.RegWrite "HKCU\Software\QlikViewPDF\OutputFile", pdfOutputFile, "REG_SZ"

WSHShell.RegWrite "HKCU\Software\QlikViewPDF\BypassSaveAs", "1", "REG_SZ"

Set WSHShell = nothing

End function

This Macro is executing the report but not saving the PDF file in a given path/it is also not creating the PDF file. I am using Windows 7 OS. Kindly assist

Thanks

Nirmal

Re: Export to PDF Macro is not working as expected

nirmalraj_kj — Tue, 24 Nov 2015 07:06:56 GMT

Any idea friends

Re: Export to PDF Macro is not working as expected

sifatnabil — Wed, 25 Nov 2015 08:09:26 GMT

Try this:

1. Install Bullzip PDF Printer.

2. Use below macro. I've bolded and highlighted the fields you should fill in. If you don't want to send an email, just exclude calling the function "zendDagrapportREPORTNAME" in the REPORTGENERATOR sub:

Sub REPORTGENERATOR

vReport = "RP02" 'Set report ID

vName = "REPORTNAME" 'Name of output pdf

ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false 'Printreport

reportFile = "C:\Data\" & vName &".pdf" 'Setting outputname

MyPrintPDFWithBullZip(reportFile) 'Call pdf printer

ActiveDocument.GetApplication.Sleep 5000

zendDagrapportREPORTNAME 'Call function to attach PDF to email

End sub

function zendDagrapportREPORTNAME()

Dim objEmail

Const cdoSendUsingPort = 2 ' Send the message using SMTP

Const cdoAnonymous = 0 'Do not authenticate

Const cdoBasic = 1 'basic (clear-text) authentication

Const cdoNTLM = 2 'NTLM

SMTPServer = "enter SMTP server"

Const SMTPPort = 25 ' Port number for SMTP

Const SMTPTimeout = 60 ' Timeout for SMTP in seconds

'Sending mail

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

vName = "REPORTNAME"

strbody = "Hello," & vbNewLine & vbNewLine & _

"Please find attached the report. Thanks." & vbNewLine & vbNewLine & _

"Regards," & vbNewLine & _

"Team X"

'objEmail.To = "x@x.com" 'Email Recipient

objEmail.From = "x@x.com" 'Email Sender

objEmail.To ="x@x.com"

objEmail.cc = "x@x.com"

'objEmail.To ="x@x.com"

objEmail.Subject = "REPORTNAME" ' Subject

objEmail.TextBody = strbody

objEmail.AddAttachment "C:\Data\" & vName &".pdf" ' Attachement

objEmail.Send

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

' msgbox ("Test Mail Sent")

end function

FUNCTION MyPrintPDFWithBullZip (pdfOutputFile)

set obj = CreateObject("Bullzip.pdfSettings")

obj.PrinterName = "Bullzip PDF Printer"

obj.SetValue "Output" , pdfOutputFile

obj.SetValue "ConfirmOverwrite", "no"

obj.SetValue "ShowSaveAS", "never"

obj.SetValue "ShowSettings", "never"

obj.SetValue "ShowPDF", "no"

obj.SetValue "RememberLastFileName", "no"

obj.SetValue "RememberLastFolderName", "no"

obj.SetValue "ShowProgressFinished", "no"

obj.SetValue "ShowProgress", "no"

obj.WriteSettings True

END FUNCTION

Re: Export to PDF Macro is not working as expected

nirmalraj_kj — Wed, 25 Nov 2015 08:24:44 GMT

Hi Saifat,

Thanks for your reply. Do you have bullzip pdf exe file.

Thanks & Regards

Nirmal

Re: Export to PDF Macro is not working as expected

sifatnabil — Wed, 25 Nov 2015 08:32:08 GMT

See attached.

Re: Export to PDF Macro is not working as expected

Thu, 25 Feb 2016 08:22:26 GMT

here is working code , i use it. you need Qlikview printer. works on XP x32, and on W7x64

SUB save

vReport="RP04" ' change your report number

printReportPDF "E:\Autoreports\QV\report1.pdf" ' you can change destination

ActiveDocument.GetApplication.Sleep 2000

ActiveDocument.PrintReport (vReport), "QlikViewPDF"

ActiveDocument.GetApplication.Sleep 10000

END SUB

'===========================================================================

Function printReportPDF(pdfOutputFile)

Set WSHShell = CreateObject("WScript.Shell")

WSHShell.RegWrite "HKCU\Software\QlikViewPDF\OutputFile", pdfOutputFile, "REG_SZ"

WSHShell.RegWrite "HKCU\Software\QlikViewPDF\BypassSaveAs", "1", "REG_SZ"

Set WSHShell = nothing

End function

Re: Export to PDF Macro is not working as expected

Thu, 25 Feb 2016 08:44:20 GMT

good code. working. (Win7 x64)