QlikTip #32: Exporting multiple QV objects to a single Excel document

QlikView supports exporting any object’s content to Excel out of the box. But if you need to export multiple objects to one Excel document you have write some macro code.

This is sometimes pain and time-consuming. Therefore I have created a code which can be easily re-used and where you only have to declare which objects should be exported and the rest will be done automatically.

The scenario

Let’s assume we have a QlikView application with some objects:

image

Now let’s have a look how we can cover the following scenarios. In all cases I am first declaring some settings (as a VBScript array) which will be passed to the function copyObjectsToExcelSheet.

(The sample application and the source code can be downloaded at the end of this article)

The idea behind this solution

My idea was to create a functionality which can be used in most of the required scenarios:

  • Adding multiple objects to a single sheet
  • Adding multiple objects to multiple sheets
  • Define a name of each sheet in Excel (instead of just using “Sheet1”, “Sheet2”, etc.)
  • Exporting either the data of an object or exporting the object/chart as an image

Therefore we first have to create an “export definition” by using a multidimensional array in VBScript:

Dim aryExport(0,3)

Definition of the four dimensions within the array:

Index Description
0 Id of the QlikView object to copy from
1 Name of the sheet (in Excel) where the object should be copied to

(If a sheet with the same name already exists no new sheet will be created, instead the existing sheet will be used for pasting the object)
Note: the sheetName can be max 31 characters long
2 Range in Excel where the object should be pasted to
3 Copy & Paste Mode [“data”, “image”]
Defines if the objects underlaying data should be pasted (“data”) or the the image representing the object should be used

Sample 1: Exporting just one object and defining a name for the sheet

'// Array for export definitions
Dim aryExport(0,3)

aryExport(0,0) = "objSalesPerYearAndRegion"
aryExport(0,1) = "Sales per Region a. Year"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'// Now either just leave Excel open or do some other stuff here
'// like saving the excel, some formatting stuff, ...

This will result into:

Exporting just one object and defining a name for the sheet

OK, OK, not really the big effort so far :) But let’s have a look at further requirements …

Sample 2: More enhanced export of three objects to three different sheets

Now let’s export three different objects to three different sheets within the same Excel document:

'// Array for export definitions
Dim aryExport(2,3)

aryExport(0,0) = "objSalesPerRegion"
aryExport(0,1) = "Sales per Region"
aryExport(0,2) = "A1"
aryExport(0,3) = "data"

aryExport(1,0) = "objTopCustomers"
aryExport(1,1) = "Top Customers"
aryExport(1,2) = "A1"
aryExport(1,3) = "data"

aryExport(2,0) = "objSalesPerYearAndRegion"
aryExport(2,1) = "Sales per Region a. Year"
aryExport(2,2) = "A1"
aryExport(2,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'// Now either just leave Excel open or do some other stuff here
'// like saving the excel, some formatting stuff, ...

Now the result is more interesting:

Exporting three QlikView objects to a single Excel document with three worksheets

Sample 3: Export of multiple objects in different formats (data & image)

But now let’s try the get most out of the script:

'// ****************************************************************
'// Export of multiple objects in different formats (data & image)
'// In one case (sheet "Sales Overview") two objects are placed on
'// one sheet.
'// ****************************************************************
Dim aryExport(4,3)

aryExport(0,0) = "objSalesPerRegion"
aryExport(0,1) = "Sales Overview"
aryExport(0,2) = "A1"
aryExport(0,3) = "image"

aryExport(1,0) = "objTopCustomers"
aryExport(1,1) = "Sales Overview"
aryExport(1,2) = "H1"
aryExport(1,3) = "image"

aryExport(2,0) = "objSalesPerYearAndRegion"
aryExport(2,1) = "Sales Overview"
aryExport(2,2) = "A14"
aryExport(2,3) = "data"

aryExport(3,0) = "objTopCustomers"
aryExport(3,1) = "Top Customers"
aryExport(3,2) = "A1"
aryExport(3,3) = "image"

aryExport(4,0) = "objTopCustomers"
aryExport(4,1) = "Top Customers"
aryExport(4,2) = "A14"
aryExport(4,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook
Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

'// Now either just leave Excel open or do some other stuff here
'// like saving the excel, some formatting stuff, ...

The result:

The resulting sheet 1 containing images and a data table

The resulting sheet 2 containing images and a data table

As always, I’ll be happy if you’d share your thoughts, ideas, comments, improvements with me!

Download Source Files:

Exporting multiple QlikView objects to a single Excel document (1.087 KB)
(2011/03/15: Updated version v1.02 with some minor bugfixes)

Bookmark and Share

and tagged , , . Bookmark the permalink. Both comments and trackbacks are currently closed.

47 Comments