I have several worksheets that contain multiple charts. I want to copy the
charts from Excel to PowerPoint with one chart to a slide. I can do so one
at a time by copying and pasting them. The drawback is that it takes lots of
time to do this because I'm working with 250+ charts. My question is if
there is a way to quickly export the charts to PowerPoint so that each slide
contains a different chart.
http://peltiertech.com/Excel/XL_PPT.html
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
"mustang25" <must...@discussions.microsoft.com> wrote in message
news:92E839B9-9C67-4276...@microsoft.com...
Unfortunately, my VB skills are at about the same level as my 2 year old
son's. In other words, I don't know the first thing about it. I figured out
how to paste the code into the VBE, but when I try to run the macro, I get an
error saying "User Type not Defined." I know this has to be a very basic
issue, but like I said, I don't know the first thing about VB. Am I better
off just copying and pasting my charts one at a time?
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
"mustang25" <must...@discussions.microsoft.com> wrote in message
news:2EC76CDA-4DFB-44A9...@microsoft.com...
This is working brilliantly. I'd like to throw one more challenge at you if
you don't mind. It follows below. Here is the code I'm using for the macro
(you'll notice that I removed the line/command that copies the chart as a
picture. This is intentional.):
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).Copy
' 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
Is there a way to output the charts to PowerPoint with the following
parameters:
Height 5.66 inches
Width 9.66 inches
Horizontal Position 0 inches from top left corner
Vertical Position 1 inch from top left corner
Many thanks in advance!
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
Instead of centering the chart, position and resize it:
With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' position the chart
With PPApp.ActiveWindow.Selection.ShapeRange
.Top = 72 ' points
.Left = 0
.Width = 9.66 * 72
.Height = 5.66 * 72
End With
End With
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"mustang25" <must...@discussions.microsoft.com> wrote in message
news:59D04694-67FF-4249...@microsoft.com...
I see what you meant in the other forum. You are right, the best way to
approach this would be to have the charts sized properly in Excel to begin
with. Be that as it may, your macro has already saved me HOURS of work in
copying each chart to its own slide. I can deal with resizing them manually
for now and will remember to size them properly in Excel the next time I do a
project like this.
Once again, many humble thanks.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"mustang25" <must...@discussions.microsoft.com> wrote in message
news:40935D84-6851-4843...@microsoft.com...
Thanks,
Danny
Any reason why you can not turn the gridlines off and back on when your
macro is run?
Cheers
Andy
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
Thanks. Turning the grid lines off\on via a macro is whjat I ended up
doing. I was just trying to find a more efficient way within the original
macro.
Project is done, but still thinking about the "better mouse trap"!
Danny
ActiveSheet.ChartObjects(iCht).CopyPicture
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Danny" <Da...@discussions.microsoft.com> wrote in message
news:77932C2D-69DF-4C1F...@microsoft.com...
My bad as I called my excel spreadsheet a chart when it is a spreadsheet.
Unless I am missing the boat, to copy a spreadsheet the range must be
selected.
Danny
http://peltiertech.com/Excel/XL_PPT.html
Look for the "Copying as a Picture Within Excel" heading a few screens down
from the top of the page.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
"Danny" <Da...@discussions.microsoft.com> wrote in message
news:97960359-4C1B-4F4D...@microsoft.com...
Thanks again! Your link cleared every thing up. I never knew the copy
picture edit box existed for use in copying a range as a picute for insertion
into PP. I was trying to copy a range. Great tip!
Danny
I've tried to find that other forum in which you said how to set the desired
chart size right in Excel, but can't. Please let me know which one it is.
Thanks.
To get back to your original question, you could adjust the pasted object's
size and position:
With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' align the chart
With PPApp.ActiveWindow.Selection.ShapeRange.
.Top = [some value]
.Left = [some value]
.Height = [some value]
.Width = [some value]
End With
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"My Own IT dept" <MyOwn...@discussions.microsoft.com> wrote in message
news:4A421EC4-4A84-43FF...@microsoft.com...
First let me say that I am a complete novice when it comes to using VBE. I
feel as if I'm in another counrty, don't understand the language and am using
hand gestures and grunting to try to get my problem solved. I'm missing some
basic understanding of how this whole system works. That said...
I'm trying to copy multiple charts (1 per worksheet) from a range of
worksheets.
I highlight the range of WS tabs and run the macro (F5). I get no error
message but nothing happens (no charts copied). I have a PPT document open
with more than enough blank pages (although I don't think that's necessary
based on what I think I read in the code).
Is there something else I should be doing?
Here is the code that I entered into the VBE editor (should look very
familiar):
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
' position the chart
With PPApp.ActiveWindow.Selection.ShapeRange
.Top = 94 ' points
.Left = 58 ' points
.Width = 8.2 * 72
.Height = 5.6 * 72
End With
End With
Next
' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Sub
-----
Any suggestions?
Thanks very much.
To change to copying chart sheets, replace these lines
For iCht = 1 To ActiveSheet.ChartObjects.Count
' copy chart as a picture
ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
with these lines
For iCht = 1 To ActiveWorkbook.Charts.Count
' copy chart as a picture
ActiveWorkbook.Charts(iCht).CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
This will copy every chart sheet to its own slide.
If you have one chart embedded in each worksheet, you can try this
variation:
For iCht = 1 To ActiveWorkbook.Worksheets.Count
' copy chart as a picture
ActiveWorkbook.Worksheets(iCht).ChartObjects(1).Chart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"My Own IT dept" <MyOwn...@discussions.microsoft.com> wrote in message
news:4751A5C2-5494-49D1...@microsoft.com...
For those of us who fight the IT battle alone you guys are a lifeline.
Mitch