XL97: Error Opening Workbook That Is Modified By Using DAO

• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, when you open a workbook, you may receive the following error message:

   This program has performed an illegal operation and will be shut down.
   If the problem persists, contact the program vendor.

   EXCEL caused an invalid page fault in module EXCEL.EXE at
   0137:3019b403.

   EXCEL caused an invalid page fault in module KERNEL32.DLL at
   0137:bff9a5d0.

CAUSE

This problem may occur if the following conditions are true:

• In Microsoft Excel 97, you run a Visual Basic for Applications macro that uses Data Access Objects (DAO) to add, remove, or modify records in a list in the workbook.

      -and-
  

• One or more of the worksheets in the workbook contains a formula that refers to any of the following functions included in the Microsoft Excel Analysis ToolPak:

        AMORDEGRC    FACTDOUBLE    NETWORKDAYS
        AMORLINC     FVSCHEDULE    NOMINAL
        BESSELI      GCD           OCT2BIN
        BESSELJ      GESTEP        OCT2DEC
        BESSELK      HEX2BIN       OCT2HEX
        BESSELY      HEX2DEC       ODDFPRICE
        BIN2DEC      HEX2OCT       ODDFYIELD
        BIN2HEX      IMABS         ODDLPRICE
        BIN2OCT      IMAGINARY     ODDLYIELD
        COMPLEX      IMARGUMENT    PRICE
        CONVERT      IMCONJUGATE   PRICEDISC
        COUPDAYS     IMCOS         PRICEMAT
        COUPDAYBS    IMDIV         QUOTIENT
        COUPDAYSNC   IMEXP         RANDBETWEEN
        COUPNCD      IMLN          RECEIVED
        COUPNUM      IMLOG10       SERIESSUM
        COUPPCD      IMLOG2        SQRTPI
        CUMIPMT      IMPOWER       TBILLEQ
        CUMPRINC     IMPRODUCT     TBILLPRICE
        DEC2BIN      IMREAL        TBILLYIELD
        DEC2HEX      IMSIN         WEEKNUM
        DEC2OCT      IMSQRT        WORKDAY
        DELTA        IMSUB         XIRR
        DISC         IMSUM         XNPV
        DOLLARDE     INTRATE       YEARFRAC
        DOLLARFR     ISEVEN        YIELD
        DURATION     ISODD         YIELDDISC
        EDATE        ISSHARED      YIELDMAT
        EFFECT       LCM
        EOMONTH      MDURATION
  
  

WORKAROUND

If this problem occurs, you cannot open the workbook. It may be possible to recover data contained within the workbook if you follow the steps shown in the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q142117
   TITLE     : Excel: Summary of Methods to Recover Data from Corrupted
               Files

   ARTICLE-ID: Q147216
   TITLE     : XL: Linking to Data in a Damaged File (5.0 and Later)

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

   Sub AddRecordToTest()

       ' IMPORTANT: Create a reference to "Microsoft DAO 3.5 Object
       ' Library" before you attempt to run this macro. To reference the
       ' library, click References on the Tools menu, click the check box
       ' for the library and click OK.

       ' Dimension variables for use by DAO.
       Dim Db As Database
       Dim Rs As Recordset

       ' Open the database in Test.xls.
       Set Db = OpenDatabase("C:\My Documents\Test.xls", _
           False, False, "Excel 8.0;")

       ' Open the list in Sheet1 of Test.xls. The list starts in cell A1.
       Set Rs = Db.OpenRecordset("Sheet1$")

       ' These commands select the last record in the list, add a new
       ' record, set the value in the first column of the list, and update
       ' the list.
       Rs.MoveLast
       Rs.AddNew
       Rs.Fields(0).Value = 1
       Rs.Update

       ' Close the list and database.
       Rs.Close
       Db.Close

   End Sub