Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Gethyn
Contributor

Loop through possible values in field and save chart to Excel for each value.

Hello,

I have a working macro as shown below which saves a chart as an Excel file based on the value selected in the fields 'PCT for Billing' and 'Month'.

At the moment I have to manually select each value in the 'PCT for Billing' field and then press a button to run the macro. What I would like to happen is for me not to have to select anything in the 'PCT for Billing' field and just press the button to run the macro and it will loop through each value in turn and save the Excel file for each.

Also I would like to be able to save the file as an Excel 2007 .xlsx file. I have tried by changing the file extension in the script to .xlsx but this is still an Excel .xls file that has just been renamed and therefore doesn't allow the extra rows that .xlsx does. I don't want the .csv file that currently gets generated when the file gets too big for .xls.


Sub ExportAnCTable1()

 

          Dim strvPCT

          Dim strvPeriod

          

        Set vPCT = ActiveDocument.Fields("PCT for Billing").GetPossibleValues

          strvPCT = vPCT.item(i).Text

          

        Set vPeriod = ActiveDocument.Fields("Month").GetPossibleValues

          strvPeriod = vPeriod.item(i).Text

          

        Set objExcel = CreateObject("Excel.Application")

          objExcel.Visible = True

          objExcel.DisplayAlerts = False

          

        set chart = ActiveDocument.GetSheetObject ("CH109")

          chart.SendToExcel

          

        Set WB = objExcel.ActiveWorkbook

          WB.SaveAs "\\belvan\Belvan_Systems\Monthly_Processing\"&strvPCT&" - A&C Backing Data for "&strvPeriod&".xls"

          

        WB.Close

          objExcel.Quit   

        chart.Minimize

          ActiveDocument.GetApplication.WaitForIdle

          ActiveDocument.ClearCache        

 

End Sub

Thanks,

Gethyn.

3 Replies
AlexOmetis
Contributor II

Re: Loop through possible values in field and save chart to Excel for each value.

If you were to use NPrinting for QlikView (www.nprinting.com), you'd be able to set this all up in the UI, using a Cycle on that field. You could then generate the Excel files on a schedule or manually and send them out to people automatically (if that's what you wanted). However I think this also only supports export to xls not xlsx...

Gethyn
Contributor

Re: Loop through possible values in field and save chart to Excel for each value.

Hi Alex,

Although your suggestion for NPrinting would work, I didn't want to have to purchase an additional piece of software.

Thanks,

Gethyn.

Employee
Employee

Re: Loop through possible values in field and save chart to Excel for each value.

I agree with you Gethyn, but if you need to perform complex operations or if you need to prepare a qvw for a big company that doesn't allow macro execution, I think it should be better to avoid macros and buy a software that is complementary to QlikView (e.g. NPrinting).

On contrary, if need a simple macro that performs a simple operation for yourself...et voilà...the macro may be the solution.

In any way, if you create an Excel.Application object you will be able to save an Excel workbook according to the currently installed Excel version...so if you have Excel 2003, you cannot create xlsx workbooks.

I have Excel 2010 and a code like yours works correctly.

Community Browser