3 Replies Latest reply: Sep 1, 2013 10:32 AM by Andrea Ghirardello RSS

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

    Gethyn Owen

      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.