11 June 2012

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") 
for i=0 to UBound(aSheetObj)
Set oSH = oXL.ActiveSheet
 Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
set obj=Nothing
oXL.Selection.Font.Bold = True
set oSH=Nothing
set oXL=Nothing
end sub

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


  1. Great macro.
    I have one question:
    I have Pivot Table with countries, towns, population and year. Countries have multiple towns and I have populaiton total for each country. When exporting using above macro,
    column A = Country,
    column B = Town,
    column C = population by year.
    Column A lists country naem just once, and I'd like to see country name repeated until we reach end of a given country.
    Is there a way to do that?

  2. If you can manage to modify the excel (i.e. the one created by this macro) with an excel macro (vba) on the file's itself, then this vba code can be integrated in the macro in this post.

  3. Thank you! This works perfect for two objects, but if I put more than two Object ID's, it would only export the first two. How can I use this code to export more than 2 objects? Thank you!


    1. I have tried it with 3 objects just by changing the object array as follows, and it worked


      Though, I realized that it fails if 2 or more objects have same captions. Because excel sheets are named with object names, and excel does not let to name 2 sheets with same name.

  4. Hi, I too had added multiple objects and it is working fine, but want to paste those objects to the body of the mail instead of a new excel worksheet or workbook

  5. good Macro, I tried to test it and is working fine, Could you please suggest me on how to save the excel sheet to specific folder with the file name

  6. I just want to save it excel to the specified folder with out any alerts..

  7. Hi, I have a qlikview report with Pivot table in which i have added a calculated dimension (interest amt) and when i export it to excel, the property of the Interest column in excel is taken as "Text", but i want that to be exported as Number or Custom.
    Is there a work around for this.I tried changing the format of column as number under settings-->document properties, but still it is the same.

    Also is it possible to add a column to the end of Pivot table in qlikview? I am trying to add the Grand Total (at row level) + Interst Amt and show that at the end of pivot table in a saperate column

  8. i have your qv file and i see the button sent to excel
    but when i press it , it only shos me the edit module and not exporting!!!
    what do i need to do?

    1. Pls. double check that Current Local Security level (in the edit module screen) is set to Allow Sytem Access level.

  9. This comment has been removed by the author.

  10. Hi All,

    Just a quick question in the reverse direction. Is it possible to export a chart from EXCEL to QLIKVIEW?

    I suppose this is not a place ask this question yet awaiting answers for the same.