Build custom charts in Excel and copy them to PowerPoint

[mustang25]

Apologies in advance for the lengthy request. I have no idea if this is even
possible to do with a macro.

I have roughly 350 lines of data that I need to create charts from. Each
line will generate its own chart. I need cylinder charts that fit the
following criteria (all chart options set to default unless noted differently
below):

960 pixels (136.43 column width) wide X 578 pixels (34 cells @ 12.75 row
height) high
The Chart Title is in the A column
The B1 cell contains the x-axis label "Min"
The C1 cell contains the x-axis label "Median"
The D1 cell contains the x-axis label "Data"
The E1 cell contains the x-axis label "Max"
The "Min" Cylinder should be yellow
The "Median" Cylinder should be blue
The "Data" cylinder should be red
The "Max" cylinder should be black
Data labels should be present, above the appropriate cylinder, and should
show value
The Chart Area should have no border and no background
Gridlines should be Gray - 25%
The Floor Area should also be Gary - 25%

After all of this, the chart should be copied to its own slide in an open
PowerPoint Presentation, the chart should delete (because of Excel's built-in
number-of-chart limitations), and the process repeat until all 350 or so rows
have been charted.

Simple, right?

Since I know next to nothing about vb, macros, and the like, it would be
much appreciated if any responses could include which references to turn on,
etc. Also, since I'm guessing that a macro like this will take some time to
write, would anyone who decides to tackle this please let me know you are
doing so? I don't mind waiting in the least, but if this is too daunting of
a challenge, I'd like to know that no one is working on it so I can get to
work on building these charts manually. Many thanks.

 

[Jon Peltier]

I would set up dynamic ranges (names). Cell F1 contains a number between 1
and 350 (or whatever number of lines you need to plot). Create names by
going to Insert menu > Names > Define. The names and their refers-to
formulas are here:

Name Refers To
MyTitle =OFFSET($A$1,$F$1,0)
MyMin =OFFSET($B$1,$F$1,0)
MyMed =OFFSET($C$1,$F$1,0)
MyDat =OFFSET($D$1,$F$1,0)
MyMax =OFFSET($E$1,$F$1,0)

So when I change the value in F1, a different row is referenced in these
names. The chart will use these names, so changing F1 changes the chart.

Make the chart using any row of data. Change the series formula as follows.

Series 1 will look like this assuming row 2 was used to create it and the
worksheet is named Sheet1:

=SERIES(Sheet1!$B$1,,Sheet1!$B$2,1)

Change it to this:

=SERIES(Sheet1!$B$1,,Sheet1!MyMin,1)

Excel is likely to change it to

=SERIES(Sheet1!$B$1,,Book1.xls!MyMin,1)

change the other cell references to the names created above.

Now you need some kind of macro. I have some sample code to copy Excel
charts to PowerPoint here:

http://peltiertech.com/Excel/XL_PPT.html

In the macro, set up the link to PowerPoint first, then set up a loop that
works like this (pseudocode):

For i=1 to 350
ActiveSheet.RangeF1.Value = i
' create a new slide (use code from the web site)
' copy the chart
' paste onto the new slide
Next


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

 

[mustang25]

Jon,

Thanks much for the reply. I fear that my inexperience is going to lead me
to ask some rather simple (to you) questions.

1 - You asked me to use the Insert > Names > Define function. Should I
Define the entire column all at once with the OFFSET formulas you listed,
just the top cell in each column, or each cell individually? If
individually, is there a unique name I should give each cell?
2 - When I redefine the series (which I'm doing after creating and
customizing the charts by using the Source Data option and replacing the
"Value" field) from "=Sheet1!$B$2:$E$2" to
"=SERIES(Sheet1!$B$1,,Sheet1!MyMin,1)" (minus the quotes, of course) I get a
"Formula contains an error" message. I must be missing something here.
3 - I'm not real sure what you meant by "change the other cell references to
the names created above." Could you clarify?

As a side note, I've used your Excel to PowerPoint macros before with
AMAZING results. I am concerned though because the macro I used only works
on existing charts and I know my computer does not have enough memory to
allow Excel to create 350 or so charts in one workbook (hence my original
post mentioning that the chart could be deleted from the workbook after it is
copied to PowerPoint). Any suggestions in this area?

Thank you again, you've personally helped me a lot in the past and I
appreciate your efforts now.

 

[Jon Peltier]

1 - You asked me to use the Insert > Names > Define function. Should I
Define the entire column all at once with the OFFSET formulas you listed,
just the top cell in each column, or each cell individually? If
individually, is there a unique name I should give each cell?

Reading ahead, I see I can simplify. I hope I didn't overcomplicate matters.
You only need the following names:

Name Refers To
MyTitle =OFFSET($A$1,$F$1,0)
MyData =OFFSET($B$1:$E$1,$F$1,0)

Each name is its own definition, and it refers to whichever cell is offest
from the top row by the number in cell F1. All the cells don't need names.

Just do Insert > Names > Define, enter the first name (MyTitle) into the
Name entrybox and the first refers ro formula (=OFFSET($A$1,$F$1,0)) into
the Refers To entrybox. Click Add, then repeat for each of the others in the
list.

I meant to say, when you create your chart, add a title, then select the
title (but don't edit it, there should be no cursor in it), click in the
formula bar, and type

=Sheet1!MyTitle

If your sheet name has a hyphen or space, enclose the name in single quotes:

='Sheet One'!MyTitle

This links the chart title to the appropriate cell in column A.

2 - When I redefine the series (which I'm doing after creating and
customizing the charts by using the Source Data option and replacing the
"Value" field) from "=Sheet1!$B$2:$E$2" to
"=SERIES(Sheet1!$B$1,,Sheet1!MyMin,1)" (minus the quotes, of course) I get
a
"Formula contains an error" message. I must be missing something here.

I explained how to edit the formula bar, above the worksheet column headers.
Change this formula:

=SERIES(Sheet1!$A$1,Sheet1!$B$1:$E$1,Sheet1!$B$2:$E$2,1)

to this:

=SERIES(Sheet1!MyTitle,Sheet1!$B$1:$E$1,Sheet1!MyData,1)

3 - I'm not real sure what you meant by "change the other cell references
to
the names created above." Could you clarify?

I thought it was more complicated than it was. Sorry.

As a side note, I've used your Excel to PowerPoint macros before with
AMAZING results. I am concerned though because the macro I used only
works
on existing charts and I know my computer does not have enough memory to
allow Excel to create 350 or so charts in one workbook (hence my original
post mentioning that the chart could be deleted from the workbook after it
is
copied to PowerPoint). Any suggestions in this area?

You are not making 350 charts. You make the one Excel chart manually, it is
updated automatically when the macro changes the value in cell F1. The same
existing chart gets copied as a picture 350 times (after each time the value
in F1 changes) and pasted into PowerPoint 350 times on a new slide. So you
do need 350 slides, but copying pictures of the chart will not cause a huge
resource bottleneck.


- Jon

 

[mustang25]

Jon,

Thanks so much for the clarification. It helped loads! The scary thing is
that I'm starting to understand some of this! I believe that I've done all
the things you outlined properly, but am having trouble with the actual
macro. I don't know the first thing about vb code, so I'm hoping I can
impose on you just one more time. Here is the code I am running:

Sub BuildCharts()
For i = 1 To 350
ActiveSheet.RangeF1.Value = i

Sub ChartsToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.Count
' copy chart as a picture
ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

' Add a new slide and paste in the chart
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' align the chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
End With

Next

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub
Next

End Sub


Obviously, I just copied and pasted the code in one of your previous replies
and did the same for one of the macros you wrote on your site. I've
evidently done something wrong. When I run this, I get an error for invalid
syntax on the third line, "ActiveSheet.RangeF1.Value = i".

Thank you once again.

 

[mustang25]

Jon,

Never mind, I figured it out! Here is the macro:

Sub BuildCharts()
For i = 1 To 350
ActiveSheet.Range("F1").Value = i

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.Count
' copy chart as a picture
ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

' Add a new slide and paste in the chart
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' align the chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
End With

Next

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

Next

End Sub


The chart has to be built somewhere in the same sheet as the data, a
PowerPoint presentation must be open, and (as you did mention) the reference
to PowerPoint must be checked. Thank you so much, you have literally saved
me DAYS of tedious work!

 

[Jon Peltier]

Sorry about the typo! Glad you figured it out.

- Jon

 

[avivexpert]

mustang25 wrote on 11/21/2006 15:25 ET :

Apologies in advance for the lengthy request. I have no idea if this i even
possible to do with a macro.

I have roughly 350 lines of data that I need to create charts from. Each
line will generate its own chart. I need cylinder charts that fit the
following criteria (all chart options set to default unless note differently
below):

960 pixels (136.43 column width) wide X 578 pixels (34 cells @ 12.75 row
height) high
The Chart Title is in the A column
The B1 cell contains the x-axis label "Min"
The C1 cell contains the x-axis label "Median"
The D1 cell contains the x-axis label "Data"
The E1 cell contains the x-axis label "Max"
The "Min" Cylinder should be yellow
The "Median" Cylinder should be blue
The "Data" cylinder should be red
The "Max" cylinder should be black
Data labels should be present, above the appropriate cylinder, and should
show value
The Chart Area should have no border and no background
Gridlines should be Gray - 25%
The Floor Area should also be Gary - 25%

After all of this, the chart should be copied to its own slide in an open
PowerPoint Presentation, the chart should delete (because of Excel' built-in
number-of-chart limitations), and the process repeat until all 350 or s rows
have been charted.

Simple, right?

Since I know next to nothing about vb, macros, and the like, it would be
much appreciated if any responses could include which references to turn on,
etc. Also, since I'm guessing that a macro like this will take some time to
write, would anyone who decides to tackle this please let me know you are
doing so? I don't mind waiting in the least, but if this is too daunting of
a challenge, I'd like to know that no one is working on it so I can get to
work on building these charts manually. Many thanks.

Hello,

For copy/paste of hundreds of charts or tables from Excel to PPT (or Word)
EzPaste-xl2anywhere is the perfect tool for you: www.EzPaste.net

Good luck
AvivExpert