Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Export to PDF / Excel

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

14 Replies
Not applicable

Export to PDF / Excel

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

Not applicable

Export to PDF / Excel

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 Smiley Happy

Not applicable

Export to PDF / Excel

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.

james
Contributor III

Export to PDF / Excel

Eugene - I am curious what you mean by control file? What have you used?

Thanks James

Not applicable

Export to PDF / Excel

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.

ier
Contributor II

Export to PDF / Excel

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.

martinvn2
New Contributor III

Export to PDF / Excel

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

Not applicable

Export to PDF / Excel

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

Not applicable

Export to PDF / Excel

Do you have any error message on the top of the screen ?

Community Browser