Microsoft KB Archive/79216

{| = Excel Macro to Print All Embedded Charts on a Worksheet =
 * width="100%"|

Last reviewed: November 30, 1994

Article ID: Q79216

SUMMARY
The following macro opens, then prints all embedded charts on a Microsoft Excel worksheet.

MORE INFORMATION
  Enter the following information into a macro sheet: A1:  Print_Charts A2:  {=SET.NAME(&quot;Chart_array&quot;,GET.DOCUMENT(42))} A3:= FOR(&quot;count&quot;,1,COLUMNS(Chart_array)) A4:= SELECT(INDEX(Chart_array,1,count)) A5:= IF(GET.OBJECT(1)=5) A6:= UNHIDE(GET.DOCUMENT(1)&&quot; &quot;&INDEX(Chart_array,1,count)) A7:= PRINT A8:= HIDE A9:= END.IF A10:= NEXT A11:= RETURN

Enter the formula in cell A2 as an array formula by pressing CTRL+SHIFT+ENTER simultaneously after typing the line.  To define the macro, select cell A1, choose Define Name from the Formula menu, select Command, and choose OK to close the dialog box. To run the macro, activate the worksheet that contains the embedded charts, choose Run from the Macro menu, select the macro from the list, and choose OK.

The GET.DOCUMENT(42) command in cell A2 of the macro creates an array of all objects on the worksheet. The macro loops through the array, selecting each object to find the embedded charts. By using GET.OBJECT(1)=5, any other objects, such as text boxes or macro buttons, are ignored. Each chart is then unhidden, printed, and then closed. Because GET.DOCUMENT(1) is used on line A6, the macro can be used on any sheet that contains embedded charts.