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

[Gethyn]

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.

[AlexOmetis]

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]

Hi Alex,

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

Thanks,

Gethyn.

[Andrea_Ghirardello]

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.