XL: Removing Chart Links to a Worksheet in a Macro

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

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()

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

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:

Q124232 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.0 4.0 5.0 break breaking

Keywords : kbmacro kbprg kbdta kbdtacode
Version : WINDOWS:3.0,4.0,5.0; :3.0
Platform : WINDOWS
Issue type : kbhowto