VFP - fetching cell with #REF! 1

[bs6600]

Hi I'm scanning the cells of a worksheet using this code

icell=loExcel.ActiveWorkbook.ActiveSheet.range(iy+ix).value

It works fine unless one of the cells has a reference to a workbook which is not avaiable (it shows #REF! in Excel).

The VFP crashes with Error 11.

Is there a better solution than ignoring Error 11?

Thanks

Bill



Bill Spence,
Dunfermline, Scotland

 

[mkrausnick]

It seems that #REF doesn't come through as anything - my test of assigning an Excel column containing #REF to a memvar yields 'DATA TYPE MISMATCH', and displaying the cell reference in the debug watch window shows "Expression could not be evaluated".

Your best solution is probably to enclose the memvar assignment in a TRY/CATCH structure and substitute another value in the memvar if the assignment fails.

Mike Krausnick
Dublin, California

 

[JRB-Bldr]

You might try something like:

* --- Select The ENTIRE Worksheet ---
oExcel.Sheets("Summary").SELECT
xlSheet = oExcel.activesheet

* --- Determine Last Cell At This Point ---
mnLastRow = oExcel.activesheet.UsedRange.ROWS.COUNT
mnLastCol = oExcel.activesheet.UsedRange.COLUMNS.COUNT

For RowCntr = 1 TO mnLastRow
   For ColCntr = 1 TO mnLastCol
     * --- Get Data From Cells That Do Not Go Beyond LastRow & LastCol ---
     ix = ALLTRIM(STR(RowCntr))
     iy = ConvCol(ColCntr)  && Convert To Alpha Char
     icell = oExcel.ActiveWorkbook.ActiveSheet.range(iy+ix).value
   ENDFOR
ENDFOR

In that way you should not be able to go beyond the LastRow and/or LastCol of your active worksheet.

Good Luck,
JRB-Bldr

 

[bs6600]

Thanks Mike and JRB. I'll try Mike's suggestion.

For JRB's interest my code is like your suggestion and the offending cells are in the used area.
I'm intrigued by your ConvCol - that's not in my VFP6 and I had to write my own.

Bill Spence,
Dunfermline, Scotland

 

[bs6600]

I just found out that Try/Catch is VFP8 so I'm going to have to save up for an upgrade!

Thanks anyway.



Bill Spence,
Dunfermline, Scotland

 

[bs6600]

I've worked around this - for your information - here's how.

in processing loop

icell=""  &&or any default value
thisform.cellerror=.t.
icell = oExcel.ActiveWorkbook.ActiveSheet.rangeiy+ix).value
*!* icell contains worksheet value or default

in thisform.error event

do case
case thisform.cellerror
    thisform.cellerror=.f.  &&ignore error in one line only
    return
case nerror=1426  && allows Excel to be running when obj created
    return
otherwise
    dodefault(nerror,cmethod,nline)
endcase

Bill Spence,
Dunfermline, Scotland

 

[JRB-Bldr]

Bill - the code I offered was copied from some working code.

The ConvCol was indeed a FUNCTION which I wrote myself to convert the numeric column references to an alpha column references.

I neglected to include it since I was merely presenting a suggestion for a general approach.

I'm glad that you got your problem resolved.

JRB-Bldr