Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can the Publisher Enterprise automatically export a PDF document or Excel file from a QlikView document before distribute it ?
If yes how ?
If no how can I do ? Maybe with scripting inside the document ?
Does anyone have this script ? If I associate the script on the open event the PDF or Excel file will be generated everytime someone open the document...so what can be do ?
Thanks a lot
Bye
This answer I wrote to another post shows how to export to Excel using macro code:
-------------------------------------------------------------------------------------------------------------------------------------
I have done something like this before within QlikView before.
There are two steps, export the image to a file, then send the file as an attachement using vbscript. Example code below (example is for Excel export, but image export would be similar, make sure to check the APIguide.qvw file for help). Also make sure to give most generous perms (System Access)to the macro or security will block sending mail.
'################################EMAIL-HANDLING FUNCTION#################################
Public Function SendMail (From,SendTo, Subject, Body, Attachment)
', Attachment, SendCC, SendBCC)
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = Subject
objMessage.From = From
objMessage.To = SendTo
objMessage.CC = SendCC
objMessage.BCC = SendBCC
objMessage.TextBody = Body
'msgbox Attachment
if( not "" = Attachment) then
objMessage.AddAttachment Attachment
end if
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "aaaaaa.corp"
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
objMessage.Send
End Function
sub email()
ActiveDocument.ClearAll False
ActiveDocument.Fields("CheckPointStart").Select ">=" & FormatDateTime( ActiveDocument.Evaluate("today(1)-30"),2)
result = ActiveDocument.Evaluate("$(thresholdTest)")
'msgbox result
if( "Fail" = result) then
Set oShell = CreateObject("WScript.Shell")
set fso = CreateObject("Scripting.FileSystemObject")
path = oShell.CurrentDirectory & "\DataIntegrity.xls"
If fso.FileExists(path) Then
set excelFile = fso.GetFile(path)
excelFile.Delete
end if
Application.Sleep(1000)
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
set obj = ActiveDocument.getSheetObject("MainTable")
obj.sendToExcel
set XLDoc = XLApp.Workbooks.item(1)
' ActiveDocument.GetSheetObject("LB03").CopyTableToClipboard true
' XLApp.Worksheets(1).Range("A1").Select()
' XLApp.Worksheets(1).Paste()
XLDoc.SaveAs path
'msgbox "pause"
XLApp.Quit
Application.Sleep(1000)
call sendmail("a@aaaaaa.com", ActiveDocument.GetVariable("sendTo").GetContent.String, "FAIL: Data Integrity test for Internal QV reports failed.", _
"A total of "&ActiveDocument.Evaluate("$(failedDataSets)") & _
" out of "&ActiveDocument.Evaluate("$(totalDataSets)") & " datasets had discrepencies which exceeded the " & _
ActiveDocument.GetVariable("warningThreshold").GetContent.String & _
" threshold. See attached Excel doc for details for last 30 days. The full QV report is here: https://aaaaaa.com/DataIntegrity_ajax/", path)
else
call sendmail("a@aaaaa.com", ActiveDocument.GetVariable("sendTo").GetContent.String, "PASS: Data Integrity test for Internal QV reports passed.", _
"All discrepencies were below the " & ActiveDocument.GetVariable("warningThreshold").GetContent.String & _
" threshold. The full QV report is here: https://aaaaa.com/DataIntegrity_ajax/", "")
end if
ActiveDocument.ClearAll False
end sub
Ok.
But can I call this function from QlikView Publisher ?
What I need is that I schedule the Excel or PDF generation and then distributing them via email.
If I put the macro in the Open Document Event this will generate a document exporting everytime I open the document, which is not what I want.
Can you help me in this ?
Thank a lot and bye 🙂
I don't know if you can call from Publisher, but I am not sure that makes sense. Perhaps you can create a copy of the document, and only open that one when you want it to send the file.
Or you can use a control file. Then set the QVW to only send out the file only when that control file exists. Then you can create the file using a scheduled event/command.
Eugene - I am curious what you mean by control file? What have you used?
Thanks James
I am sorry, it sounds more fancy than it is. I am using "control file" like "control flag". It is an empty file, but it's existence is tested by the QV load script or Macro, and used to make a decision of whether to export. Then the file is deleted. The file can be created by a scheduled task, and thus control the export from QV.
Yes. You will need to write macro script. For Excel, you just need to utilize the Excel Object Library and the QlikView API to paste the contents into an Excel workbook object as Eugene suggested. Then you can control Excel through the workbook object just as if you were writing macros directly in Excel.
I would use a variable as a flag to indicate whether these operations should be performed conditonally or not. Possibly the qvuser()? Maybe the time the qvw is accessed?
You can probably do the same with pdfs using any type of pdf export software.
There are a variety of custom ways to actually handle the email aspect of it.
Eugene Please help me. I am trying to use a section of your code in an Qlikview macro. Basically I need to open and paste the content of a straight table in the worksheet. Your sample does exactly that but it keeps failing on the first line. "Set XLApp = CreateObject("Excel.Application")"
Function CopySendToExcel
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
set obj = ActiveDocument.getSheetObject("CHEXL01")
' obj.sendToExcel
set XLDoc = XLApp.Workbooks.item(1)
ActiveDocument.GetSheetObject("CHEXL01").CopyTableToClipboard true
XLApp.Worksheets(1).Range("A1").Select()
XLApp.Worksheets(1).Paste()
XLDoc.SaveAs "Financial_Report.xls"
'msgbox "pause"
XLApp.Quit
End Function
Did you gave the system access to the module.If not then give select Aloow system access in the module security and current local security and it should work
Talha
Do you have any error message on the top of the screen ?