Article ID: 176783
Article Last Modified on 11/23/2006
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q176783
SYMPTOMS
You get the following error message when you run a macro that sends data to Microsoft Excel 97 for Windows:
When you click Close, you get the following error message:
CAUSE
This problem occurs when you do the following:
- You use DDEPOKE to send data to Excel. -and-
- The active sheet of the workbook is a Chart sheet.
WORKAROUND
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:
Use either of the following methods to prevent this problem from reoccurring.
Method 1: Change the Active Sheet
This problem does not occur if the active sheet is a worksheet. To change the active sheet to a worksheet, use the DDEExecute statement to send the Workbook.Activate command to change the active sheet. For example, the following macro sends the string "this is a test" to cell A1 of Sheet1 in the workbook Book1.
Sub Example1() chan = DDEInitiate ("Excel", "[Book1]Sheet1") DDEExecute chan, "[Workbook.Activate(""Sheet1"")]" DDEPoke chan, "R1C1", "this is a test" DDETerminate chan End Sub
NOTE: You must know the exact name of the worksheet you are switching to.
Method 2: Use DDEExecute
Use the DDEExecute statement with the Formula function instead of using the DDEPoke statement. For example, the following macro sends the string "this is a test" to cell A1 of Sheet1 in the workbook Book1.
Sub Example2() chan = DDEInitiate ("Excel", "[Book1]Sheet1") DDEExecute chan, "[Formula(""this is a test"",""'Sheet2'!R1C1""]" DDETerminate chan End Sub
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.
MORE INFORMATION
Dynamic Data Exchange (DDE) is a method you can use to exchange data with another DDE-compliant program.
For additional information about using DDE commands in Microsoft Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:
141772 XL: Visual Basic Examples Using DDE
REFERENCES
For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:
163435 VBA: Programming Resources for Visual Basic for Applications
Additional query words: XL97
Keywords: kbbug kberrmsg kbprogramming KB176783