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") 
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




12 comments:

  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?

    ReplyDelete
  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.


    ReplyDelete
  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!

    ReplyDelete
    Replies

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

      aSheetObj=Array("TB01","CH01","CH02")

      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.

      Delete
  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

    ReplyDelete
  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

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

    ReplyDelete
  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

    ReplyDelete
  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?

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

      Delete
  9. This comment has been removed by the author.

    ReplyDelete
  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.

    Thanks.,
    Sam

    ReplyDelete