Skip to main content
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
Showing results for 
Search instead for 
Did you mean: 

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


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



        Set WB = objExcel.ActiveWorkbook

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








End Sub



3 Replies
Partner Ambassador
Partner Ambassador

If you were to use NPrinting for QlikView (, 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...

Qlik Partner Ambassador 2024

Hi Alex,

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




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 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 if you have Excel 2003, you cannot create xlsx workbooks.

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