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