Hi,
I am trying to write a macro that will loop through multiple reports per region selection and export each report to excel. I loaded (Inline) the Report Names and corresponding object codes into the application to facilitate, however I cannot seem to get the code to
Loop properly through the various selections:
The first region selected seems to work - I get a tab per each report I selected, but when the code moves to the second region selected it continues to export ALL the reports (regardless of the report name selection).
Can anyone point me in the right direction?
Sub vTotal_TestLooping_version3
set doc= ActiveDocument
set docprop = doc.GetProperties
doc.GetApplication.Refresh
set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True
appExcel.WorkBooks.Add()
set RptReg = ActiveDocument.Fields("ReportingRegion").GetPossibleValues
For j=0 to RptReg.Count-1
ActiveDocument.Fields("ReportingRegion").Select RptReg.Item(j).Text
Set RptName = ActiveDocument.Fields("ReportName").GetPossibleValues
For i=0 to RptName.Count-1
ActiveDocument.Fields("ReportName").Select RptName.Item(i).Text
set RptObjCode = ActiveDocument.Fields("ObjectCode").GetPossibleValues
For k=0 to RptObjCode.Count-1
ActiveDocument.Fields("ObjectCode").Select RptObjCode.Item(k).Text
Set ExportObject = doc.GetSheetObject(RptObjCode.Item(k).Text)
appExcel.ActiveSheet.Name = (RptReg.Item(j).Text&" "&RptName.Item(i).Text)
ExportObject.CopyTableToClipboard True
appExcel.ActiveSheet.Paste
appExcel.Sheets.Add
ActiveDocument.Fields("ObjectCode").Clear
NEXT
ActiveDocument.Fields("ReportName").Clear
NEXT
ActiveDocument.Fields("ReportingRegion").Clear
NEXT
End sub
