-
QlikView – Export chart data on specific date to xls file
Posted on September 18th, 2008 2 commentsHello,
today is very useful day :-D
In this post i’ll show you how to expand and export (in excel format) pivot table on specific date for each value in list box using sub macros.So the situation is that you want to generate excel files for each country (for example) on specific date. Instead to do this manual you can achieve this using macros (VBScript) in QlikView. Below is the code:
sub Exports// Add dates on which files will be generated in arrayDim dateList(3)
dateList(0) = #01/08/2008#
dateList(1) = #15/08/2008#
dateList(2) = #18/09/2008#// For each value in array chech that match todayFor each reportDate in dateList
If reportDate = Date Then// Exported table will be CH01set Table = ActiveDocument.GetSheetObject("CH01")// Clear all selections that was madeActiveDocument.ClearAll false// Get all values in Country field
set val=ActiveDocument.Fields("Country").GetPossibleValues// For each Country do
for i=0 to val.Count-1// Select each country
ActiveDocument.Fields("Country").Select val.Item(i).Text
// Expand third cell in first column
set chart = ActiveDocument.GetSheetObject("CH01")
chart.ExpandLeft 1,0,false// Expand second cell in first column
set chart = ActiveDocument.GetSheetObject("CH01")
chart.ExpandLeft 1,0,false// Expand Expand all cells in first column
set chart = ActiveDocument.GetSheetObject("CH01")
chart.ExpandLeft 0,0,true// Get data in CH01
set obj = ActiveDocument.GetSheetObject("CH01")
// Save data from table in file.
//Example for file name: Projects_Bulgaria.xls
obj.ExportBiff "C:\Projects_"&val.Item(i).Text&".xls"
next
End If
NextAfter creating this macros you may put it in OnPostReload (Settings –> Document Properies –> Macros) event in the document so after each reload this macros will be trigerred.
