Microsoft KB Archive/172356: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 164: | Line 164: | ||
<pre class="codesample"> Sub AddRecordToTest() | <pre class="codesample"> Sub AddRecordToTest() | ||
' IMPORTANT: Create a reference to | ' IMPORTANT: Create a reference to "Microsoft DAO 3.5 Object | ||
' Library | ' Library" before you attempt to run this macro. To reference the | ||
' library, click References on the Tools menu, click the check box | ' library, click References on the Tools menu, click the check box | ||
' for the library and click OK. | ' for the library and click OK. | ||
Line 174: | Line 174: | ||
' Open the database in Test.xls. | ' Open the database in Test.xls. | ||
Set Db = OpenDatabase( | Set Db = OpenDatabase("C:\My Documents\Test.xls", _ | ||
False, False, | False, False, "Excel 8.0;") | ||
' Open the list in Sheet1 of Test.xls. The list starts in cell A1. | ' Open the list in Sheet1 of Test.xls. The list starts in cell A1. |
Latest revision as of 11:06, 21 July 2020
Article ID: 172356
Article Last Modified on 11/23/2006
APPLIES TO
- 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:
If you click Details, you receive an error message similar to either of the following:
-or-
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:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
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