Qlik Community

QlikView Documents

Documents for QlikView related information.

Automated Excel Export and Email Macro

autopilot
Contributor III

Automated Excel Export and Email Macro

The intended purpose is to automate the exporting of an object to a predefined file location in Excel format unless the chart renders no data in which it would export a confirmation file in .txt format. For the Excel export portion, there exists the ability to iterate over a field's values within the dashboard for the exported file. Then, It would take the corresponding file and attach it to an email - sent via SMTP to a predefined recipient list. The sequence is cascaded through a series of files - primarily in order to trigger the execution without interfering with the existing (production) dashboard. This includes utilizing an additional qvw in order to start the .bat file which enables the scheduling within the QMC.

I've attached a .zip folder containing all the required documents to view the set up as intended. It can easily be adapted or taken apart to meet a variety of needs.

Labels (1)
Attachments
Comments
manojkvrajan
Contributor III

I tried this feature with a button trigger. However it dint work in my server. Macro dint throw an error. Am I missing something?

0 Likes
autopilot
Contributor III

Hi,

Sorry for delay in my response....I was not aware that a comment had been made.

I may need more information to better understand your set up and assist in troubleshooting. But, generally speaking you should be able to execute the feature using a button trigger.

The button would need to contain two run macro actions in this order using the provided code.

Run Macro > xlsExport

Run Macro > MailReport

If you still have trouble, let me know and I'll try to help assist in troubleshooting.

0 Likes
manojkvrajan
Contributor III

Thank you. Appreciate your response.. I got it working

0 Likes
Not applicable

How set user and password for autenthicate user smtp?

0 Likes
autopilot
Contributor III

In the macro "MailReport" there are several constants that control authentication. I've adapted this macro to enable the basic authentication method and included it below. Changes to the script are noted. Copy/paste script into the edit module area within qlikview and set the variables with your details.

For more general information, here is a link that you may find helpful:

http://www.paulsadowski.com/wsh/cdo.htm

SUB MailReport

    DIM objEmail

    DIM varDate

    DIM v

    SET v = ActiveDocument.Variables("vDate")

          varDate = v.GetContent.STRING

    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

'******************************************

'added for basic authentication setup

    CONST cdoUserName = xxxxx 'username for using basic auth

    CONST cdoPassword = xxxxx ' password for using basic auth

'******************************************

    SMTPServer = "[Insert Address]"

    CONST SMTPPort = 25                 ' Port number for SMTP

    CONST SMTPTimeout = 60              ' Timeout for SMTP in seconds

    'Sending mail

    SET objEmail = CREATEOBJECT("CDO.Message")

    SET objConf = objEmail.Configuration

    SET objFlds = objConf.Fields

    WITH objFlds

        '---------------------------------------------------------------------

           ' SMTP server details

           .Item("http://schemas.microsoft.com/cdo/configuration/sENDusing") = cdoSENDUsingPort

           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer

           .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic 'THIS WAS CHANGED FROM cdoAnonymous to cdoBasic

           .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = cdoUserName 'THIS PARAMETER WAS ADDED FOR BASIC AUTH SETUP

           .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = cdoPassword    'THIS PARAMETER WAS ADDED FOR BASIC AUTH SETUP

           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPPort

           .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = FALSE

           .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = SMTPTimeout

           .Update

        '---------------------------------------------------------------------

    END WITH

    objEmail.To = "[Insert Email]"        'Email Recipient

    'objEmail.Cc = "[Insert Email]"          'Carbon Copy Recipient

    objEmail.From = "[Insert Email]"                        'Email Sender

    objEmail.SUBject = "QlikView Automated Excel Report"                        ' Subject

    objEmail.TextBody = "QV Report - Excel File Attached. This email is an automated procedure generated by QlikView."        'Text Body         

    objEmail.AddAttachment   "c:\export-email-automation\ExcelReport_"&varDate&".xls"    ' Attachment

    objEmail.SEND

    SET objFlds = NOTHING

    SET objConf = NOTHING

    SET objEmail = NOTHING

END SUB

0 Likes
Partner
Partner

Thanks Autopilot. It is really helpful

0 Likes
georges_galvan
New Contributor II

Hi autopilot,

Your script is very usseful.

I adapt it to obtain differents Excel spreadsheets from a huge repport.


But, I have a problem when I schedule it via the qemc, ever using your method (a qvw that launchs a .bat, that then launchs the final qvw -your dashboard- by using a variable to activate the script) it doesn't work.

The qemc stays in his "running green triangle" and does nothing.


Do you know if there are a limitation that concerns the qemc in using your method?

I saw something concerning that the qemc is blocking the possibility to launch a qv.exe, ever via .bat files...

0 Likes
autopilot
Contributor III

I don't think it matters....but what version of QV are you using?

Will you elaborate what you mean by "it doesn't work" / "...does nothing" and I can try to assist troubleshooting. At what point does it seem to fail......or I guess what I'm asking is - what does it do?

For a quick fix, you could always simply trigger the .bat held in the initial qvw on a schedule using windows scheduler which should work-around the issue your having with qmc.

0 Likes
georges_galvan
New Contributor II

Hi autopilot,

I'm using qv server V10. (10.00.9061.7)

I create in the qemc a task associated with the qvw that launch the .bat (your Call_BAT_Application.qvw).

When I run the task, the task passes from the waiting status to the running status and stay there. I see that the .qvw generates a .log that indicates that the execution has started, and thats all. The only way to stop the task in the qemc is to abort the task.

For test purpose, I changed the .bat that executes this qvw (instead of the TriggerMacro.bat) and I executed a .bat that copy/paste a file... and this works fine... I guess that the problem is related to the fact that to execute QV.exe from the qemc in not allowed by Qlikview... Should you confirm this or has you tried this in another qemc?

If there is no other choice, I agree with you to workaround this via the windows scheduler.

Regards

0 Likes
georges_galvan
New Contributor II

I answerd in other windows.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-01-28 04:14 PM
Updated by: