Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trapping #REF in code (A2K SR1 XL97 SR2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trapping #REF in code (A2K SR1 XL97 SR2)

    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:

    <pre>Dim blnCellError As Boolean

    On Error Resume Next

    blnCellError = (Mid(objXLSheet.Cells(intRow, intCol).Formula, 3, 4) = "#REF" )

    If Err<>0 Then
    blnCellError = True
    End If

    If blnCellError Then
    intPos = 1
    Else
    intPos = InStr(objXLSheet.Cells(intRow, intCol), strQuote)
    End If</pre>

    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping #REF in code (A2K SR1 XL97 SR2)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trapping #REF in code (A2K SR1 XL97 SR2)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •