topic Export to PDF / Excel in QlikView

Export to PDF / Excel

Mon, 26 Jan 2026 18:19:17 GMT

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

Export to PDF / Excel

Tue, 19 May 2009 04:35:48 GMT

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

Export to PDF / Excel

Wed, 20 May 2009 00:55:19 GMT

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 🙂

Export to PDF / Excel

Wed, 20 May 2009 01:10:38 GMT

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.

Export to PDF / Excel

james — Fri, 05 Jun 2009 20:02:26 GMT

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

Thanks James

Export to PDF / Excel

Fri, 05 Jun 2009 21:17:50 GMT

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.

Export to PDF / Excel

Michael_Reese — Thu, 11 Jun 2009 01:18:20 GMT

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.

Export to PDF / Excel

Anonymous — Thu, 11 Jun 2009 20:44:17 GMT

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

Export to PDF / Excel

Thu, 11 Jun 2009 20:52:43 GMT

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

Export to PDF / Excel

Thu, 11 Jun 2009 21:52:51 GMT

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

Export to PDF / Excel

Thu, 11 Jun 2009 22:52:55 GMT

Though obvious, another thing to keep in mind is that Publisher loads qvw files using the execution service using qvb.exe. This execution should be on a computer running in an account with excel installed.

Export to PDF / Excel

Anonymous — Mon, 15 Jun 2009 13:04:17 GMT

I checked the Qlikview Security, the settings are correct.

It seems to fail at different lines. I think it might have something to do with Excel security. I am running MS Excel 2007?

The Qlikview model is still running in a development environment with administrator user rights and MS Excel running locally.

Export to PDF / Excel

Tue, 16 Jun 2009 19:53:32 GMT

Your application is running on your QV Server no ?

Yes it is the case, do you have excel installed on that server ?

Thanks

Philippe

Export to PDF / Excel

Wed, 17 Jun 2009 16:30:16 GMT

No, you don't need to have Excel installed on the server.

Best regards
Stefan

Re: Export to PDF / Excel

etay_netintent — Mon, 27 Aug 2012 13:05:42 GMT

It is possible to run the macro from an external VBS file.

Place the working macro code inside the VBS file, Create a DOC object and change all of the ActiveDocument to DOC.

then olave the Cscript command to run the VBS inside a batch, and run the batch when the publisher reload ends as an external task.

We have a daily export of an excel after loading with the publisher.

Good Luck

Etay