'Qlikview Macro VBScript to print pdf and email will not run consistently - Fails in email
I have been tearing my hair out over the last few days in trying to get this macro to work consistantly on the Windows scheduler.
Basically the workflow is as follows:
1) Windows Scheduler - Daily, uses Admin user credentials
2) Batch file - Reloads using /l
3) Reloads Qlikview application, which has triggers on post reload to save a pdf and email it using PDF Xchange and an html formatted e-mail to cover mobile.
I am getting such inconsistent behaviour that I cannot isolate the problem to any particular one thing. Sometimes it works, sometimes it doesn't. More often than not it fails on the Windows scheduler. There is no error since QV has just thrown up the VBScript window in the hidden process.
I've been changing permissions, which helped me reach a level of inconsistent performance as opposed to no performance.
In addition, it appears that you cannot pass variables to the PDF Xchange printer.
The code in the macro is as follows:
sub ExportPDF
printReportPDF "\\SGH-SRV-FPS1\S-Drive\eCommerce\Data Analyst\Reporting\Daily E-Commerce Report\E-Commerce Daily Report.pdf"
ActiveDocument.GetApplication.Sleep 2000
ActiveDocument.PrintReport "RP01", "PDF-XChange 3.0"
ActiveDocument.GetApplication.Sleep 8000
end sub
Function printReportPDF(pdfOutputFile)
Set WSHShell = CreateObject("WScript.Shell")
WSHShell.RegWrite "HKCU\Software\Tracker Software\PDF-XChange 3.0\OutputFile", pdfOutputFile, "REG_SZ"
WSHShell.RegWrite "HKCU\Software\Tracker Software\PDF-XChange 3.0\BypassSaveAs", "1", "REG_SZ"
Set WSHShell = nothing
End function
Sub ExportEmail
Dim strvDest 'as string
strvDest = ActiveDocument.Variables("vDestination").GetContent().String
msgbox(strvDestination)
Define report variables
get the date as a serial for the filename output
Export an Object
Set obj = ActiveDocument.ActiveSheet.SheetObjects("TX25")
Set obj1 = ActiveDocument.ActiveSheet.SheetObjects("TX17")
Set obj2 = ActiveDocument.ActiveSheet.SheetObjects("TX18")
Set obj3 = ActiveDocument.ActiveSheet.SheetObjects("TX15")
Set obj5 = ActiveDocument.ActiveSheet.SheetObjects("CH62")
Set obj6 = ActiveDocument.ActiveSheet.SheetObjects("TX16")
Set obj8 = ActiveDocument.ActiveSheet.SheetObjects("CH58")
Set obj9 = ActiveDocument.ActiveSheet.SheetObjects("TX31")
Set obj10 = ActiveDocument.ActiveSheet.SheetObjects("CH69")
msgbox("defined objects")
obj.ExportBitmapToFile "D:\QlikView\SGP-UDA\QVS_Source\UserApp\MainLogo.jpg"
obj1.ExportBitmapToFile "D:\QlikView\SGP-UDA\QVS_Source\UserApp\MainHeader.jpg"
obj2.ExportBitmapToFile "D:\QlikView\SGP-UDA\QVS_Source\UserApp\DateRange.jpg"
obj3.ExportBitmapToFile "D:\QlikView\SGP-UDA\QVS_Source\UserApp\SecondaryHeader.jpg"
obj5.ExportBitmapToFile "D:\QlikView\SGP-UDA\QVS_Source\UserApp\DailySiteDetail.jpg"
obj6.ExportBitmapToFile "D:\QlikView\SGP-UDA\QVS_Source\UserApp\SecondaryHeader2.jpg"
obj8.ExportBitmapToFile "D:\QlikView\SGP-UDA\QVS_Source\UserApp\WeeklySiteDetail.jpg"
obj9.ExportBitmapToFile "D:\QlikView\SGP-UDA\QVS_Source\UserApp\SecondaryHeader3.jpg"
obj10.ExportBitmapToFile "D:\QlikView\SGP-UDA\QVS_Source\UserApp\WeeklySiteDetailLW.jpg"
msgbox("created objects")
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
Const SMTPServer = "xxxx" ' changed for public consumption
Const SMTPPort = 25 ' Port number for SMTP
Const SMTPTimeout = 120 ' Timeout for SMTP in seconds
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields
With objFlds
———————————————————————
SMTP server details
removed the html links down to this being my first post
.Update
———————————————————————
End With
allow the passing of a variable from the load script to define the distribution list
if len(strvDest) > 0 then
msgbox("variable exists "&strvDest)
objEmail.To = strvDest
else
msgbox("variable does not exist")
objEmail.To = "xxxx" 'changed for public consumption
end if
objEmail.From = "xxxx" 'changed for public consumption
objEmail.Subject = "Daily Reporting"
HTML = "<!DOCTYPE HTML PUBLIC ""-//IETF//DTD HTML//EN"">" & chr(13) & chr(10)
HTML = HTML & "<html>"
HTML = HTML & "<head>"
HTML = HTML & "<meta http-equiv=""Content-Type"" content=""text/html; charset=iso-8859-1"">"
HTML = HTML & "<title>Automated Emails!</title>"
HTML = HTML & "</head>"
HTML = HTML & "<body bgcolor=""#FFFFFF"">"
HTML = HTML & "<br> <img src=""cid:MainLogo.jpg"" >"
HTML = HTML & "<br> <img src=""cid:MainHeader.jpg"" >"
HTML = HTML & "<br> <img src=""cid:DateRange.jpg"" >"
HTML = HTML & "<br> <img src=""cid:SecondaryHeader.jpg"" >"
HTML = HTML & "<br> <img src=""cid:DailySiteDetail.jpg"" >"
HTML = HTML & "<br> <img src=""cid:SecondaryHeader2.jpg"" >"
HTML = HTML & "<br> <img src=""cid:WeeklySiteDetail.jpg"" >"
HTML = HTML & "<br> <img src=""cid:SecondaryHeader3.jpg"" >"
HTML = HTML & "<br> <img src=""cid:WeeklySiteDetailLW.jpg"" >"
HTML = HTML & "<p>"
HTML = HTML & "</p>"
HTML = HTML & "</body>"
HTML = HTML & "</html>"
Set objBP = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\MainLogo.jpg", "MainLogo.jpg", CdoReferenceTypeName)
objBP.Fields.Item("urn:schemas:mailheader:Content-ID") = "<MainLogo.jpg>"
objBP.Fields.Update
Set objBP1 = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\MainHeader.jpg", "MainHeader.jpg", CdoReferenceTypeName)
objBP1.Fields.Item("urn:schemas:mailheader:Content-ID") = "<MainHeader.jpg>"
objBP1.Fields.Update
Set objBP2 = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\DateRange.jpg", "DateRange.jpg", CdoReferenceTypeName)
objBP2.Fields.Item("urn:schemas:mailheader:Content-ID") = "<DateRange.jpg>"
objBP2.Fields.Update
Set objBP3 = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\SecondaryHeader.jpg", "SecondaryHeader.jpg", CdoReferenceTypeName)
objBP3.Fields.Item("urn:schemas:mailheader:Content-ID") = "<SecondaryHeader.jpg>"
objBP3.Fields.Update
Set objBP5 = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\DailySiteDetail.jpg", "DailySiteDetail.jpg", CdoReferenceTypeName)
objBP5.Fields.Item("urn:schemas:mailheader:Content-ID") = "<DailySiteDetail.jpg>"
objBP5.Fields.Update
Set objBP6 = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\SecondaryHeader2.jpg", "SecondaryHeader2.jpg", CdoReferenceTypeName)
objBP6.Fields.Item("urn:schemas:mailheader:Content-ID") = "<SecondaryHeader2.jpg>"
objBP6.Fields.Update
Set objBP8 = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\WeeklySiteDetail.jpg", "WeeklySiteDetail.jpg", CdoReferenceTypeName)
objBP8.Fields.Item("urn:schemas:mailheader:Content-ID") = "<WeeklySiteDetail.jpg>"
objBP8.Fields.Update
Set objBP9 = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\SecondaryHeader3.jpg", "SecondaryHeader3.jpg", CdoReferenceTypeName)
objBP9.Fields.Item("urn:schemas:mailheader:Content-ID") = "<SecondaryHeader3.jpg>"
objBP9.Fields.Update
Set objBP10 = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\WeeklySiteDetailLW.jpg", "WeeklySiteDetailLW.jpg", CdoReferenceTypeName)
objBP10.Fields.Item("urn:schemas:mailheader:Content-ID") = "<WeeklySiteDetailLW.jpg>"
objBP10.Fields.Update
Set objBPDoc = objEmail.AddRelatedBodyPart("D:\QlikView\SGP-UDA\QVS_Source\UserApp\Qlikview Printing.pdf", "Qlikview Printing.pdf", CdoReferenceTypeName)
objBPDoc.Fields.Item("urn:schemas:mailheader:Content-ID") = "<Qlikview Printing.pdf>"
objBPDoc.Fields.Update
objEmail.HTMLBody = HTML
msgbox("attached objects")
objEmail.Send
Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing
ActiveDocument.Save
Application.Quit
End Sub
Solution 1:[1]
I cannot ask questions so I will try to give you an answer of the bat.
Firstly, does it reload manually from the QV Document? If yes then
- Then you are most probably using 2 differant usernames to reload, please make sure that the username used to reload via scheduler has permissions on every single file the VB is accessing.
Did you set the permissions on the VB side to the following:
- Requested Module Security = System Access
- Current Local Security = Allow system access
Lastly, On the General screen of the scheduled task, under security options at the bottom. Make sure "Run with highest privileges" is unticked, this sometimes causes issues.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Shaun |