lcBaseReportFileName = lcCobraProgramReportsDir + CPR5CA_REPORT_NAME
IF !FILE( lcBaseReportFileName )
BRSERROR( "Couldn't locate report " + lcBaseReportFileName )
RETURN .F.
ENDIF
llOpenSourceBookErr = .F.
TRY
*--open the base report
loSourceReportBook = loExcelApp.Workbooks.Open( lcBaseReportFileName )
CATCH TO loErr
llOpenSourceBookErr = .t.
AddLogLine( "Couldn't open report " + lcBaseReportFileName + CRLF +;
"Error: " + TRANSFORM( loErr.ErrorNo) + CRLF +;
"Description: " + loErr.Message )
ENDTRY
*-- make sure we didn't get an error opening the source workbook
IF llOpenSourceBookErr
BRSERROR( "Could open report " + lcBaseReportFileName )
RETURN .f.
ENDIF
llOpenSourceSheetErr = .F.
TRY
*--open the sheet
loSourceReportSheet = loSourceReportBook.Sheets( COBRA_REPORTS_SHEETNAME )
CATCH TO loErr
llOpenSourceSheetErr = .t.
AddLogLine( "Couldn't locate Reports sheet in " + lcBaseReportFileName + CRLF +;
"Error: " + TRANSFORM( loErr.ErrorNo) + CRLF +;
"Description: " + loErr.Message )
ENDTRY
*-- make sure we didn't get an error opening the Reports sheet
IF llOpenSourceSheetErr
BRSERROR( "Couldn't locate Reports sheet in " + lcBaseReportFileName )
RETURN .f.
ENDIF
*--add a sheet to the cams notebooks for the cpr5
loManagerNotebookCpr5Sheet = loManagerNotebook.Sheets.Add()
loManagerNotebookCpr5Sheet.Name = CPR5CA_SHEETNAME
*--loop through the cursor of accounts for this CAM/PEM
IF !USED( lcMgrTableName )
RETURN
ENDIF
lnLastRow = loSourceReportSheet.Cells(65536, 1).End(xlUp).Row
loSrchRng = loSourceReportSheet.Range( "A1:A" + Num2TStr( lnLastRow ) )
lnPasteCount = 0
SELECT ( lcMgrTableName )
SCAN
loFoundRng = .F.
lcAccount = RTRIM( CA1 ) + SPACE(1) + RTRIM( CA2 ) + SPACE(1) + RTRIM( DESCRIP )
lnFoundRow = 0
*--search col A for this account
loFoundRng = loSrchRng.Find( lcAccount, , xlValues, xlPart, xlByColumns, xlNext, .f. )
*--make sure a match was found, if not then add a log entry and loop to the next account
IF ISNULL( loFoundRng )
AddLogLine( "Account " + lcAccount + " not found in " + lcBaseReportFileName )
LOOP
ENDIF
lnFoundRow = loFoundRng.Row
*--build range spec in "RC:RS" notation like "A1:J30"
lcThisAccountRng = "A" + Num2TStr( lnFoundRow - 2 ) + ":" + CPR5_LAST_COL_STRING + Num2TStr( lnFoundRow + 20 )
*--copy this account from the source report
loSourceReportSheet.Range( lcThisAccountRng ).Copy
*--paste into cam notebook
*--all the data in cobra reports are formulas, so have to paste value then paste formats
lcPasteDestRng = "A" + Num2TStr( CPR5_ROWS_PER_ACCOUNT * lnPasteCount + ( lnPasteCount + 1 ) )
loManagerNotebookCpr5Sheet.Range( lcPasteDestRng ).PasteSpecial( xlPasteValues )
loManagerNotebookCpr5Sheet.Range( lcPasteDestRng ).PasteSpecial( xlPasteFormats )
llCamNotebookDirtyFlag = .t.
lnPasteCount = lnPasteCount + 1
ENDSCAN
loSourceReportBook.Close(.f.)