KB172356 - XL97: Error Opening Workbook That Is Modified By Using DAO

Article ID: 172356

Article Last Modified on 11/23/2006

Microsoft Excel 97 Standard Edition

This article was previously published under Q172356

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.

If you click Details, you receive an error message similar to either of the following:

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

-or-

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. You may be able to recover data contained within the workbook if you follow the steps shown in the following articles in the Microsoft Knowledge Base:

142117 Excel: Summary of Methods to Recover Data from Corrupted Files

147216 XL: Linking to Data in a Damaged File (5.0 and Later)

To prevent this problem, do not use Data Access Objects to add or modify records in a Microsoft Excel 97 workbook if the workbook contains formulas that refer to functions in the Analysis ToolPak.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.

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 professionals 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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

In Microsoft Excel 97, you can use Data Access Objects in a Visual Basic for Applications macro to add, remove, or modify records in a database. If the database is in a Microsoft Excel 97 workbook, you can add a record to the list in the workbook by using a macro similar to the following sample code:

   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.

       ' 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

If you run this macro, a new record containing the value 1 is added to the list in Sheet1 of Test.xls. However, if Test.xls contains a formula that refers to a function included in the Analysis ToolPak, you cannot open Test.xls after running this macro.

Additional query words: XL97 ipf atp

Keywords: kbbug kbcode kberrmsg kbprogramming KB172356