14 Replies Latest reply: Aug 27, 2012 9:05 AM by etay elazar RSS

    Export to PDF / Excel

    sayeds

      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
          ewasserman

          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
              sayeds

              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
                  ewasserman

                  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
                  Martin van Niekerk

                  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
                  Mike Reese

                  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.

                  • Re: Export to PDF / Excel
                    etay elazar

                    Hi

                    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