Excel: Removing Chart Links to a Worksheet in a Macro

Last reviewed: September 2, 1997
Article ID: Q78463

The information in this article applies to:
  • Microsoft Excel for Windows versions 3.0, 4.0, 5.0
  • Microsoft Excel for OS/2, version 3.0

SUMMARY

A chart may be made independent of a worksheet by using a Microsoft Excel macro to convert any links to the worksheet to values.

MORE INFORMATION

To convert a series formula to values manually, activate the formula bar by pressing F2 and then press F9 to convert the formula to values. To accomplish this from a macro, use the SEND.KEYS command. This must be done for each series formula in the chart.

Because keystrokes sent with the SEND.KEYS command are typically not executed until a dialog box is displayed or a RETURN is executed, attempting to send keystrokes in a looping structure does not work properly. The following macro simulates a loop and works properly because the RETURN statement is executed after each SEND.KEYS statement. Note that the macro may not run properly if you attempt to step through it.

Example

  1. Enter the following into a macro sheet:

          A1: ConvertLinks
          A2: i=0
          A3: i=i+1
          A4: =SELECT("S"&i)
          A5: =SEND.KEYS("{F2}{F9}~")
          A6: =IF(i<GET.DOCUMENT(11),ON.TIME(NOW()+"00:00:01","R3C1"))
          A7: =RETURN()
    

  2. Define the macro by selecting cell A1 and choosing Define Name from the Formula menu. Select the Command option and choose OK.

  3. To run the macro, activate any chart and choose Run from the Macro menu. Select ConvertLinks from the list of macros and choose OK.

The macro works by setting a counter "i" to zero. This counter is used to select each individual series in the chart and is incremented each time the macro is run. The SEND.KEYS command converts the links to values. The IF statement tests to see whether all series formulas have been converted by comparing the current value of "i" to the number of series in the chart. If not, the current macro ends but is started again in one second.

For more information on this topic, query on:

   on.time and send.keys and excel

For an article that describes this same subject using Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q124232
   TITLE     : XL5: Visual Basic Macro to Break Chart Links

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 2, 64, 159, 217


Additional query words: 3.00 3.0 4.0 4.00 5.0 5.00 break breaking
Keywords : kbmacro kbprg PgmHowTo PgmOthr
Version : 3.00 4.00 5.00 | 3.00
Platform : OS/2 WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.