Sending Qlikview Reports in Excel

Would not it be nice if we could send some qlikview charts by email to a recipient automatically?

By using qlikview OCX and a piece of C# code, we can do this!!!

Let's examine the attached solution:

1) We need an excel template under template folder in which the position and the name of the qlikview chart to be transferred into excel is defined.
For example:
In the screenshot below, the definition CH01;image;0.5 in cell A1 means, export the chart CH01 from qlikview as an image and resize it by factor 0.5 and position its top-left corner with cell A1.
The definition CH02;text in cell H1 means, export the chart CH02 from qlikview as text and position its top-left cell with cell H1.

2) The output folder is used to copy the template into with a unique name and transfer the data from qlikview to this unique excel file before it is sent by email.

3) The qlikview file(s) should be placed in the same directory as template and output folder.

4) The excel template should have the same name as qlikview file. For example: if the qlikview file is named as Contoso-Simple.qvw then the excel file should be named as Contoso-Simple.xlsx.

5) The email account credentials which will be used to send the email are stored in app.config file.


How to use the application:

Before using the application, provide the email account credentials in app.config file which is under reportertest\Reporter\Reporter folder.

1) Manual use: double click on the reporter.exe under reportertest\Reporter\Reporter\bin\Debug folder. In the opening screen:

• select the folder where qlikview files and template and output folders are located
• select the qlikview file
• make any selection in the qlikview file shown in the big frame
• enter email address to which the excel report will be sent
• click on Send XLS

2) Automatic use:  The reporter.exe can also be called from console or scheduled to run automatically in windows scheduled tasks. While calling from console or scheduling it requires five arguments:

arg 1: full path of the file

arg 2: user name (if section access is used in qlikview file)

arg 3: user password (if section access is used in qlikview file)

arg 4: selections to be made in the qlikview file 

arg 5: the email address to which the report will be sent

For example: The following console command will open the reporter application, access the contoso-simple.qvw file, then make the selections (20071 and 20072 as CalendarQuarter & Online as ChannelName) and send out the excel report to xyz@gmail.com.

Reporter.exe "C:\\Users\\Bilge\\Desktop\\HappyStartinUSA\\OCX\\Contoso-Simple.qvw" "" "" "<CalendarQuarter:number:20071,20072><ChannelName:text:Online>" "xyz@gmail.com"

Source Code

Here is the source code and sample files (template, qvw file): reportertest.zip

Qlikview: Macro: How to export multiple objects into excel

By the help of macros, we can export data from multiple objects (table charts, table boxes) into excel.

All we need to do is to pass the Object IDs in the highlighted line of the macro code below and run the macro by a Button.

Pls. note that Current Local Security level (in the edit module screen) should be set to Allow Sytem Access level.

Macro code:

sub launchXL
set oXL=CreateObject("Excel.Application") 
oXL.visible=True
oXL.Workbooks.Add
aSheetObj=Array("TB01","CH01")
for i=0 to UBound(aSheetObj)
oXL.Sheets.Add
Set oSH = oXL.ActiveSheet
 oSH.Range("A1").Select    
 Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste 
sCaption=obj.GetCaption.Name.v
set obj=Nothing
oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True
oSH.Cells.Select
oXL.Selection.Columns.AutoFit
oSH.Range("A1").Select    
oSH.Name=left(sCaption,30)
set oSH=Nothing
next
set oXL=Nothing
end sub


Here is a sample qlikview application: https://sites.google.com/site/quickdevtips/home/excelmacro.qvw?attredirects=0&d=1