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
Subscribe to:
Post Comments (Atom)
Great macro.
ReplyDeleteI 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?
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.
ReplyDeleteThank 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
DeleteI 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.
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
ReplyDeletegood 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
ReplyDeleteI just want to save it excel to the specified folder with out any alerts..
ReplyDeleteHi, 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.
ReplyDeleteIs 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
i have your qv file and i see the button sent to excel
ReplyDeletebut when i press it , it only shos me the edit module and not exporting!!!
what do i need to do?
Pls. double check that Current Local Security level (in the edit module screen) is set to Allow Sytem Access level.
DeleteThis comment has been removed by the author.
ReplyDeleteHi All,
ReplyDeleteJust 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
Hello thank you for your VBA Code, but i want to know how make working that on the qlikview server, when i try to export from Access point ( Server), that doesn't work
ReplyDelete