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

  1. Lewis
    Posted July 5, 2012 at 10:45 | Permalink

    Stefan

    Really nice Macro

    Just a couple of questions
    1) why will the macro not run on the Ajax zero footprint?
    2) is it possible to incorporate into the macro similar ability to export the selections the users have made? (in the same way selecting the BIFF option in the User Preferences Export selection stamps)

    Thanks

    Lewis

    • Posted August 21, 2012 at 09:28 | Permalink

      Hi Lewis,

      ad 1) it is not supported … but instead you could use a script file (WSH, VBScript) on the server which executes the script … e.g. automatically …

      ad 2) Just create a “Current Selections” box and reference this object and you’ll achive that …

      Regards
      Stefan

      • AJ
        Posted November 12, 2012 at 09:24 | Permalink

        Hello Stefan,

        I am trying to use this macro but getting a Type Mismatch on copyObjectsToExcelSheet.

        I understand that this function must be included/declared but I am unable to figure out how.

        Please can you help? Also I am using PE so can’t directly open your file.

        Regards

        Akbar

      • vinodh
        Posted March 27, 2014 at 10:45 | Permalink

        Dear Stefan,

        Thanks for providing Macro.

        1. while auto reloading the application macro need to run and Excels will be save in particular path. this actions are not happen in above macro.

        so if you have the save the Excels macro, share me.

        Thanks&Regards,
        vinodh c

  2. Gerhard
    Posted July 11, 2012 at 17:47 | Permalink

    Hi there,

    I am using QlikView PE so cannot open your file – and I am VERY new to Macros.

    Any way you can post the full macro?

    I get the Type mismatch: ‘copyObjectsToExcelSheet’ error.

    Thanks,

    Gerhard

  3. Emma
    Posted July 30, 2012 at 12:43 | Permalink

    Hi,

    This macro is exactly what I need … but I can’t make it work!

    I’m getting an error :Paste method of Worksheet class failed

    Any ideas?

    Many thanks,
    Emma

    • Emma
      Posted July 30, 2012 at 13:12 | Permalink

      Hi
      Fixed the problem – I have some charts that show or hide based on a variable which I forgot might be an issue. It can’t copy a hidden table!

      :-)

      Excellent script!

      Emma

  4. Emma
    Posted July 30, 2012 at 13:20 | Permalink

    Got another question now though. I have seen similar requests from other people with your advice on fixing them, but my vb isn’t up to mastering what I need to do. So … any chance you can explain how I might manage the following:

    Export two charts to multiple sheets with a different field selection for each one, and different sheet name. So for example, select London in region field and save Chart1 and Chart2 to a sheet names “London”, then select West Country in the region field and save Chart1 and Chart2 to a sheet names “West Country” etc. etc.

    I looked at the code and thought adding a second dimention might work, or potentially using the sheet name as the selection somehow. I did also have a think about havng a list of selections and trying to run through those in a for each loop type way, but then couldn’t figure out how to make this add new sheets to the same doc rather than creating a new one each time.

    ANy ideas or clues I can follow?

    Many thanks,
    Emma

  5. Bumin
    Posted August 14, 2012 at 00:41 | Permalink

    thanks Stefan,

    this is what I need to do but one question:
    how can I automatically save the excel-sheet and close the macro?

    kind regards
    Bumin

    • Michael
      Posted August 17, 2012 at 12:12 | Permalink

      im sure you will get the answer by reading the 2nd comment.

  6. Michael
    Posted August 16, 2012 at 08:57 | Permalink

    Thanks for sharing.
    CopyToClipBoard… it will not retain the Leading Zero (Even your column is Text data type) once copied to Excel. I’m stil figuring how to keep the Leading Zero without doing the Macro in Excel VBA.

    obj.SendToExcel might will be better choice, HOWEVER it couldn’t copy to multiple sheets to multiple excel worksheets.

    • Michael
      Posted August 17, 2012 at 12:10 | Permalink

      The only way i can think of is to use CopyTextToClipBoard instead of CopyTableToClipBoard (true); well, its format will be dropped.

      Anyone has better solution?

  7. Ian
    Posted August 17, 2012 at 10:17 | Permalink

    Hi

    Great script , exactly what I’m after! One thing the objects I export to excel fully maximise on the screen within Qlikview and then exports it to excel. Is there any way to avoid this happening? Or after maximising and export the objects returns to there previous size (as is when opening Qlikview and before export)?

    I guess it makes sense to maximise the object when you are exporting objects to excel in particular an ‘image’. Just a little annoyance more than anything. After I export double clicking the object header shrinks the objects back to original size… Thats a work around but would be nice if it didn’t happen / reverted back.

    Any help or pointers be much appriciated.
    Cheers
    Ian

    • Michael
      Posted August 17, 2012 at 11:23 | Permalink

      Ian,
      To avoid the maximising.. just comment the

      'objSource.Maximize

      • Ian
        Posted August 17, 2012 at 12:07 | Permalink

        excellent thanks.

  8. Adrien
    Posted August 20, 2012 at 17:07 | Permalink

    Hello everyone,

    First of all, thank you for sharing those tips. I am quite new with VB and QV so my questions might be ridiculous…

    1) How can I export a text object in Excel, in addition to the multiple objects i already have (mainly charts) ?

    Currently, i can export complex objects like charts so i wonder why i can’t even export a simple text object…
    FYI, i simply copied/pasted the code given in the sample.

    2) In a previous comment, someone asked how to manage the width of the columns and Stefan WALTHER gave the logic answer to use VB autofit function :

    “Worksheets(“Sheet1”).Columns(“A:I”).AutoFit”

    Here is a view of the code I am using :

    ========================================================
    sub exportToExcel_TAB

    Dim aryExport(2,3)

    aryExport(0,0) = “CH23”
    aryExport(0,1) = “the name of my 1st sheet”
    aryExport(0,2) = “A1”
    aryExport(0,3) = “data”

    aryExport(1,0) = “CH58”
    aryExport(1,1) = “the name of my 2nd sheet”
    aryExport(1,2) = “A1”
    aryExport(1,3) = “data”

    aryExport(2,0) = “CH68”
    aryExport(2,1) = “the name of my 3rd sheet”
    aryExport(2,2) = “C16”
    aryExport(2,3) = “data”

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

    WorkSheets(“the name of my 1st sheet”).Columns(“A:I”).AutoFit

    ‘// Now either just leave Excel open or do some other stuff here
    ‘// like saving the excel, some formatting stuff, …
    ============================================================

    • Adrien
      Posted August 20, 2012 at 17:10 | Permalink

      I forgot a part of the comment…

      “”””

      Could anyone give me some advices or solutions?

      Thanks in advance for the help,
      Adrien

  9. Joe
    Posted September 24, 2012 at 03:15 | Permalink

    Stefan
    We have used the macro for the export function and it has work really well and has saved us a lot of time.
    We were wondering if there was a way to export multiple charts to one excel sheet but only include certain information (certain expressions from these charts) to populate one excel sheet. We have about 7 expressions in each chart and all expressions are named consistently throughout all charts. We only need use of 3 of the expressions from each chart and need only these 3 to carry over to the one excel sheet. And most importantly we need it all to populate into one excel spreadsheet with all the information stacked on top of each other Is this possible?

    Thanks for all you help
    Joe

  10. masin
    Posted September 26, 2012 at 18:12 | Permalink

    i put this application in an access-point and i have a matrix 130*4 and i have this error:
    macro parse failed functionality was lost

    anyone can help me please?
    thaks in adavance
    Marchetto

  11. Leonard
    Posted October 12, 2012 at 01:01 | Permalink

    Stefan

    For the question :
    1) why will the macro not run on the Ajax zero footprint?

    Your answer was:
    • ad 1) it is not supported … but instead you could use a script file (WSH, VBScript) on the server which executes the script … e.g. automatically …

    Do you have any example for script file (WSH, VBScript)?

  12. Posted October 14, 2012 at 20:13 | Permalink

    whoah this weblog is fantastic i like reading your articles.
    Keep up the great work! You realize, many persons are hunting around
    for this info, you could aid them greatly.

  13. Alejo
    Posted November 9, 2012 at 21:50 | Permalink

    Hi Stefan & All,

    Thanks for sharing this it has helped me a lot!!
    I am trying to export 3 charts within a container and the tabs are created however with the same chart, which in fact would be the one active at that moment.
    I have tried making the container active before running the macro but without luck and neither by updating the macro.

    Any help will be greatly appreciated!!
    Thanks,
    Alejo

  14. Alex
    Posted November 14, 2012 at 19:40 | Permalink

    Stefan thank´s a lot!!
    I required somthing like this and it works fine.
    I´m new in this website and it´s amazing!!

    Regards,
    Alex

  15. Alex
    Posted November 15, 2012 at 17:21 | Permalink

    Hi!
    I want to change the table´s design generated like borders, colors…
    Is it possible??

    Thanks,
    Alex

  16. Astrid Hermann
    Posted November 22, 2012 at 22:05 | Permalink

    Vielen Dank für diesen Code. Er wird mir sehr viel Zeit sparen.

    Viele Grüße
    Astrid

  17. Astrid Hermann
    Posted November 23, 2012 at 18:24 | Permalink

    Hello Stefan,

    your code helped a lot already. Only one thing i can’t solve without further helb:

    I habe 9 sheets in my excel, all have the same settings with different objects. Now all i want is to freezepanes them in field I4. I tried several hints i fount in the web, still i cannot make it work. My settings are like this:

    sub exportToExcel_BS
    ‘// Array for export definitions

    Dim aryExport(8,3)

    aryExport(0,0) = “objBSM1”
    aryExport(0,1) = “M1”
    aryExport(0,2) = “A1”
    aryExport(0,3) = “data”

    objExcelWorkbook.Worksheets (“M1”).Columns(“A”).ColumnWidth = 8
    objExcelWorkbook.Worksheets (“M1”).Columns(“B:D”).ColumnWidth = 14
    objExcelWorkbook.Worksheets (“M1”).Columns(“A:D”).WrapText = True
    objExcelWorkbook.Worksheets (“M1”).Columns(“A:D”).ShrinkToFit = True
    objExcelWorkbook.Worksheets (“M1”).Columns(“G:R”).Style = “Currency”
    …and so on….

    Can you help me with the correct code. I know it must be something like:
    Range(“I4”).Select
    ActiveWindow.FreezePanes = True

    but all i tried does not work

    regards
    Astrid

  18. Michele
    Posted January 5, 2013 at 19:44 | Permalink

    Hello Stefan,

    thanks a lot for your code, you have almost fixed all my excel exporting problems!!!

    I’m a newbie on VBS so I need some help that for you will look stupid 😛

    I cannot save the file once I have prepared it with your function!!! it’s all about ExcelApplication and ExcelWorkbook but I can’t find the right solution, this is what I tried so far:

    sub exportToExcel_Variant1

    ‘// 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”

    set XLApp = CreateObject(“Excel.Application”)
    XLApp.Visible = true
    XLApp.DisplayAlerts = False
    Dim XLAppgg ‘as Excel.Workbook
    Set XLAppgg = XLApp.Workbooks.Add
    Set XLAppgg = copyObjectsToExcelSheet(ActiveDocument, aryExport)
    XLApp.ActiveWorkbook.Saveas “C:\test.xls”,FileFormat=xlExcel9795

    ‘// Now either just leave Excel open or do some other stuff here
    ‘// like saving the excel, some formatting stuff, …
    end sub

    Can you please help me? thanks a lot!!

    Best Regards
    Michele

    • Michele
      Posted January 7, 2013 at 17:15 | Permalink

      Hello Stefan,

      I solved the saving in my own way, I added a 5th cell to the Array and passed the saving name to your code. Now my only problem is the leading zero as someone else wrote before and got no answers:

      I’m exporting codes which are like “00354…” and even if they are text fields they become numbers and lose the zeros…. is there any simple solution?

      Thank you!

      Best Regards
      Michele

      • Michael
        Posted January 8, 2013 at 11:13 | Permalink

        read my reply?

        • Michele
          Posted January 8, 2013 at 14:22 | Permalink

          Hi Michael,

          Thanks for your answer and happy new year! :)
          if you are refering to the Copytexttoclipboard answer, yes I read that one but it didn’t work well for me because I lost the titles together with the format…

          I solved the problem in my own way :
          1)changing the value in the column with leading zeros adding ‘COD ‘ in the qlikview table 2)changing the format of the whole excel sheet to text
          3)removing the ‘COD ‘ from the column

          It works allright but I’d like some cleaner solution!!!

  19. Asela
    Posted February 22, 2013 at 19:21 | Permalink

    Hi! I get the next error

    El componente ActiveX no puede crear el objeto: ‘Excel.Application’

    ActiveX component can’t create the object ‘Excel.Application’

    I’m new at macros, pls help!!

    Thnx

    • Alvaro
      Posted May 28, 2014 at 14:56 | Permalink

      Hi, i have the same problem someone have a soluttion ??
      Hola, tengo el mismo problema, alguien tiene la solución?

      Thanks
      Gracias!

  20. Chriss Mervin
    Posted March 7, 2013 at 10:48 | Permalink

    Hello Stefan,

    I will be very thank full to you giving such a wonderful macro to export objects to excel. I will be using this macro more than year its perfectly works what I expected, in the platform of windows xp and Ms office 2010.

    Currently our clients moved to Windows 7 and the same Ms office 2010, the macro is working perfectly but the text objects background become dark, the background colour is automatically filled in the Ms Excel.

    As I tried to set a property like.background = None that is not working.

    Hope I am clear, Is there any solution to hide the background colour.

    Kind Regards

    Chriss

  21. Monika
    Posted November 18, 2013 at 16:44 | Permalink

    hi

    how can you save the excel file by using a variable? what I mean is using the actual name of a company i run the macro for (one one previously selected from my List Box)?

    thanks

  22. Sandy
    Posted November 28, 2013 at 16:17 | Permalink

    Hi,
    All need help with the vbs-macros.
    Looks great on Microsoft Windows Script 5.6 documentation.

  23. Gerhard
    Posted December 4, 2013 at 10:01 | Permalink

    Hi,

    Please can someone show me how to save the file:

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

  24. Brian G
    Posted December 5, 2013 at 21:29 | Permalink

    The Macro works great. My only problem as one user mentioned its will only work on the active chart within a Container. How do we overcome this? I guess I will have to create a duplicate version of my charts but I heard if its hidden they wont work either?

    Thanks,
    Brian

  25. Rohan
    Posted January 7, 2014 at 08:46 | Permalink

    HI Stefan,

    Thanks for the blog and we could create a Export to excel feature. Few questions:

    1) At access point suppose user click on the button(containing vbscript) will the excel be generated without any issue on users machine Or there are any limitations to this export to excel feature by using VBscripts?

    2) If there are any limitations how to overcome those so that export to excel feature works smoothly on any local machine through access point

  26. Harshita gaur
    Posted January 21, 2014 at 07:48 | Permalink

    Hi everyone,
    can anyone please tell me, what is the full VBscript i have to write for exporting multiple charts in one sheet. i am using the PE version of qlikview. So i cant open the QVW which is provided here

  27. Majid
    Posted January 21, 2014 at 11:39 | Permalink

    Hi Stefan,

    Thanks a lot for great post. It works 100% on application level, but via web browser dosn’t work. Do you have any advice or suggestion?

    Regards,
    Majid

  28. Peter Sobisch
    Posted January 21, 2014 at 12:13 | Permalink

    Hallo Stefan,

    ich denke, ich habe das gleiche Problem wie Majid. Das Makro funktioniert einwandfrei wenn ich die Applikation starte, wenn ich die Datei aber über den browser öfffne, kann ich das makro zwar starten, aber es exportiert nicht nach Excel. Ich kann allerdings einzelne Felder mit der Option “send to Excel” nach Excel exportieren. Fehlt im Dokument irgendeine Einstellung oder woran kann es liegen?

    MfG
    Peter

  29. Peter Sobisch
    Posted January 24, 2014 at 13:17 | Permalink

    HI,
    anyone else a solution? I can extract from the application but not via web browser. If I try to extract only one field to excel it works but if I try to extract via makro, nothing happens. Security is set for external objects.

  30. Posted May 4, 2014 at 06:14 | Permalink

    Adore the ѕite and we Һave bookmarked
    it, remember to add more content.

  31. Gabriele Boccassini
    Posted May 19, 2014 at 16:59 | Permalink

    Is there a way to grouping/merge all the objects in a single group via macro?

  32. Stefan
    Posted June 9, 2014 at 17:33 | Permalink

    Hello Stefan,

    This article help me a lot, but what I need is to somehow calculate number of rows in each table object and set next qlikview object two or three rows below previous one. Can you help me to manage this?

    Dim aryExport(4,3)

    aryExport(0,0) = “objSalesPerRegion”
    aryExport(0,1) = “Sales Overview”
    aryExport(0,2) = usedrows
    aryExport(0,3) = “image”

    aryExport(1,0) = “objTopCustomers”
    aryExport(1,1) = “Sales Overview”
    aryExport(1,2) = usedrows + 2
    aryExport(1,3) = “image”

    aryExport(2,0) = “objSalesPerYearAndRegion”
    aryExport(2,1) = “Sales Overview”
    aryExport(2,2) = usedrows
    aryExport(2,3) = “data”

    aryExport(3,0) = “objTopCustomers”
    aryExport(3,1) = “Top Customers”
    aryExport(3,2) = usedrows + 2
    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)

  33. Posted July 16, 2014 at 16:55 | Permalink

    Hi Stefan!

    thanks for a great post.
    if i wish my pivot table to be export and each row populated instead of showing the pivot as is in qlikview, is it possible to modify script?

    for example if i right click and export a Qlikview object i get row based information but with the macro the dimesions do not populate on each row.

    Best
    Brad