Trapping #REF in code (A2K SR1 XL97 SR2)

Topic

New Reply

I am writing a procedure to read through a series of Excel 97 spreadsheets and convert the contents into an Access 2000 database.
However, several of the cells in the sheets have errors in their formulas, e.g.

=(#REF!-C7)/0.0125

What I want to do is report the error and save the details into an Access table. The problem is that however I try to reference the cell I keep getting Error 2023 come up on the first line of the following code :

If Mid(objXLSheet.Cells(intRow, intCol).Formula, 3, 4) = “#REF” Then
intPos = 1
Else
intPos = InStr(objXLSheet.Cells(intRow, intCol), strQuote)
End If

Any ideas on how I can stop this happening ?

Thanks for your help

Nick

Reply | Quote

Replies

I think you’re going to have to trap the 2023 error and handle it accordingly. You can do it several ways, the most primitive being this:

Dim blnCellError As Boolean
 
On Error Resume Next
 
blnCellError = (Mid(objXLSheet.Cells(intRow, intCol).Formula, 3, 4) = "#REF" )

If Err0 Then
    blnCellError = True
End If
 
If blnCellError Then
    intPos = 1
Else
    intPos = InStr(objXLSheet.Cells(intRow, intCol), strQuote)
End If

Reply | Quote

Charlotte

Thanks for your reply. I also posted this on the Excel board, and I received this helpful tip :

If TypeName(objXLSheet.Cells(intRow, intCol).Value) = “Error” Then…

which has done the trick exactly.

Nick

Reply | Quote

Wonderful! That’s better automation code anyhow, since it uses the Worksheet objects methods and properties.

Reply | Quote