W
Woody
I'm writing a program in visual foxpro that involves copy/pasting data out
of Excel spreadsheets. When I'm done with all of the copy/pastes, the
sheet displays the last range I pasted which is aften way down from the
top. I'd like the active cell to be A1. When I tried:
loCamNotebookCpr5Sheet.Range("A1").Select
I got error:
"Error: 1329
OLE IDsipatch exception code 0 from Microsoft Office Excel: Unable to get
the Select property of the Range class"
I got the same error with:
loCamNotebookCpr5Sheet.Range("A1").Activate
If it helps, here's the whole procedure:
*!
***************************************************************************
**
*!
*! Procedure: FormatCPR5CA
*!
*!
*!
***************************************************************************
**
LPARAMETERS lcCobraProgramReportsDir, loCAMNotebook, loExcelApp
LOCAL loSourceReportBook, loSourceReportSheet, lnLastRow,
loCamNotebookCpr5Sheet, llCamNotebookDirtyFlag, ;
lnPasteCount, loSrchRng, loFoundRng, lcThisAccountRng,
lcPasteDestRng
TRY
*--open the base report
loSourceReportBook = loExcelApp.Workbooks.Open(
lcCobraProgramReportsDir + 'cpr5ca.xls' )
loSourceReportSheet = loSourceReportBook.Sheets( "Report" )
CATCH
BRSERROR( "Could open base report " + lcCobraProgramReportsDir
+ 'cpr5ca.xls' )
RETURN
ENDTRY
*--add a sheet to the cams notebooks for the cpr5
loCamNotebookCpr5Sheet = loCAMNotebook.Sheets.Add
loCamNotebookCpr5Sheet.Name = CPR5_SHEETNAME
*--loop through the cursor of accounts for this CAM/PEM
IF !USED( "_csrCAMCA" )
RETURN
ENDIF
lnLastRow = loSourceReportSheet.Cells(65536, 1).End(xlUp).Row
loSrchRng = loSourceReportSheet.Range( "A1:A" + Num2TStr( lnLastRow )
)
lnPasteCount = 0
SELECT _csrCAMCA
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 TYPE( 'loFoundRng' ) # 'O'
AddLogLine( "Account " + lcAccount + " not found in " +
lcCobraProgramReportsDir + 'cpr5ca.xls' )
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 ) )
loCamNotebookCpr5Sheet.Range( lcPasteDestRng ).PasteSpecial(
xlPasteValues )
loCamNotebookCpr5Sheet.Range( lcPasteDestRng ).PasteSpecial(
xlPasteFormats )
llCamNotebookDirtyFlag = .t.
lnPasteCount = lnPasteCount + 1
ENDSCAN
loCamNotebookCpr5Sheet.Range("A1").Activate
*--save if we made any changes
IF llCamNotebookDirtyFlag
loCAMNotebook.Save
ENDIF
*--close the source report if it's still around
IF TYPE( 'loSourceReportBook' ) = 'O'
loSourceReportBook.Close
ENDIF
RETURN
of Excel spreadsheets. When I'm done with all of the copy/pastes, the
sheet displays the last range I pasted which is aften way down from the
top. I'd like the active cell to be A1. When I tried:
loCamNotebookCpr5Sheet.Range("A1").Select
I got error:
"Error: 1329
OLE IDsipatch exception code 0 from Microsoft Office Excel: Unable to get
the Select property of the Range class"
I got the same error with:
loCamNotebookCpr5Sheet.Range("A1").Activate
If it helps, here's the whole procedure:
*!
***************************************************************************
**
*!
*! Procedure: FormatCPR5CA
*!
*!
*!
***************************************************************************
**
LPARAMETERS lcCobraProgramReportsDir, loCAMNotebook, loExcelApp
LOCAL loSourceReportBook, loSourceReportSheet, lnLastRow,
loCamNotebookCpr5Sheet, llCamNotebookDirtyFlag, ;
lnPasteCount, loSrchRng, loFoundRng, lcThisAccountRng,
lcPasteDestRng
TRY
*--open the base report
loSourceReportBook = loExcelApp.Workbooks.Open(
lcCobraProgramReportsDir + 'cpr5ca.xls' )
loSourceReportSheet = loSourceReportBook.Sheets( "Report" )
CATCH
BRSERROR( "Could open base report " + lcCobraProgramReportsDir
+ 'cpr5ca.xls' )
RETURN
ENDTRY
*--add a sheet to the cams notebooks for the cpr5
loCamNotebookCpr5Sheet = loCAMNotebook.Sheets.Add
loCamNotebookCpr5Sheet.Name = CPR5_SHEETNAME
*--loop through the cursor of accounts for this CAM/PEM
IF !USED( "_csrCAMCA" )
RETURN
ENDIF
lnLastRow = loSourceReportSheet.Cells(65536, 1).End(xlUp).Row
loSrchRng = loSourceReportSheet.Range( "A1:A" + Num2TStr( lnLastRow )
)
lnPasteCount = 0
SELECT _csrCAMCA
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 TYPE( 'loFoundRng' ) # 'O'
AddLogLine( "Account " + lcAccount + " not found in " +
lcCobraProgramReportsDir + 'cpr5ca.xls' )
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 ) )
loCamNotebookCpr5Sheet.Range( lcPasteDestRng ).PasteSpecial(
xlPasteValues )
loCamNotebookCpr5Sheet.Range( lcPasteDestRng ).PasteSpecial(
xlPasteFormats )
llCamNotebookDirtyFlag = .t.
lnPasteCount = lnPasteCount + 1
ENDSCAN
loCamNotebookCpr5Sheet.Range("A1").Activate
*--save if we made any changes
IF llCamNotebookDirtyFlag
loCAMNotebook.Save
ENDIF
*--close the source report if it's still around
IF TYPE( 'loSourceReportBook' ) = 'O'
loSourceReportBook.Close
ENDIF
RETURN