Excel: Removing Chart Links to a Worksheet in a Macro

• 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.

For more information on this topic, query on:

   on.time and send.keys and excel

   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