close

OK, I think I have a question that will stump even the best of you!

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.

Partway through this article you'll find programmatic approaches to that
very problem:

peltiertech.com/Excel/XL_PPT.html

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

quot;mustang25quot; gt; wrote in message
...
gt; OK, I think I have a question that will stump even the best of you!
gt;
gt; I have several worksheets that contain multiple charts. I want to copy
gt; the
gt; charts from Excel to PowerPoint with one chart to a slide. I can do so
gt; one
gt; at a time by copying and pasting them. The drawback is that it takes lots
gt; of
gt; time to do this because I'm working with 250 charts. My question is if
gt; there is a way to quickly export the charts to PowerPoint so that each
gt; slide
gt; contains a different chart.
It amazes me to see the amount of knowledge stored on these forums. I'm
seriously impressed that there is an answer to my question. Thank you Jon.

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 quot;User Type not Defined.quot; 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?

quot;Jon Peltierquot; wrote:

gt; Partway through this article you'll find programmatic approaches to that
gt; very problem:
gt;
gt; peltiertech.com/Excel/XL_PPT.html
gt;
gt; - Jon
gt; -------
gt; Jon Peltier, Microsoft Excel MVP
gt; Peltier Technical Services
gt; Tutorials and Custom Solutions
gt; PeltierTech.com/
gt; _______
gt;
gt; quot;mustang25quot; gt; wrote in message
gt; ...
gt; gt; OK, I think I have a question that will stump even the best of you!
gt; gt;
gt; gt; I have several worksheets that contain multiple charts. I want to copy
gt; gt; the
gt; gt; charts from Excel to PowerPoint with one chart to a slide. I can do so
gt; gt; one
gt; gt; at a time by copying and pasting them. The drawback is that it takes lots
gt; gt; of
gt; gt; time to do this because I'm working with 250 charts. My question is if
gt; gt; there is a way to quickly export the charts to PowerPoint so that each
gt; gt; slide
gt; gt; contains a different chart.
gt;
gt;
gt;

I suspect you didn't set the reference to PowerPoint. It's described higher
on the page than the specific code you must have copied. Just under the big
title quot;Activating Other Applications with Excel VBAquot;.

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

quot;mustang25quot; gt; wrote in message
...
gt; It amazes me to see the amount of knowledge stored on these forums. I'm
gt; seriously impressed that there is an answer to my question. Thank you
gt; Jon.
gt;
gt; Unfortunately, my VB skills are at about the same level as my 2 year old
gt; son's. In other words, I don't know the first thing about it. I figured
gt; out
gt; how to paste the code into the VBE, but when I try to run the macro, I get
gt; an
gt; error saying quot;User Type not Defined.quot; I know this has to be a very basic
gt; issue, but like I said, I don't know the first thing about VB. Am I
gt; better
gt; off just copying and pasting my charts one at a time?
gt;
gt; quot;Jon Peltierquot; wrote:
gt;
gt;gt; Partway through this article you'll find programmatic approaches to that
gt;gt; very problem:
gt;gt;
gt;gt; peltiertech.com/Excel/XL_PPT.html
gt;gt;
gt;gt; - Jon
gt;gt; -------
gt;gt; Jon Peltier, Microsoft Excel MVP
gt;gt; Peltier Technical Services
gt;gt; Tutorials and Custom Solutions
gt;gt; PeltierTech.com/
gt;gt; _______
gt;gt;
gt;gt; quot;mustang25quot; gt; wrote in message
gt;gt; ...
gt;gt; gt; OK, I think I have a question that will stump even the best of you!
gt;gt; gt;
gt;gt; gt; I have several worksheets that contain multiple charts. I want to copy
gt;gt; gt; the
gt;gt; gt; charts from Excel to PowerPoint with one chart to a slide. I can do so
gt;gt; gt; one
gt;gt; gt; at a time by copying and pasting them. The drawback is that it takes
gt;gt; gt; lots
gt;gt; gt; of
gt;gt; gt; time to do this because I'm working with 250 charts. My question is
gt;gt; gt; if
gt;gt; gt; there is a way to quickly export the charts to PowerPoint so that each
gt;gt; gt; slide
gt;gt; gt; contains a different chart.
gt;gt;
gt;gt;
gt;gt;
Wow! You, sir, are my hero! Thank you very much!

quot;Jon Peltierquot; wrote:

gt; I suspect you didn't set the reference to PowerPoint. It's described higher
gt; on the page than the specific code you must have copied. Just under the big
gt; title quot;Activating Other Applications with Excel VBAquot;.
gt;
gt; - Jon
gt; -------
gt; Jon Peltier, Microsoft Excel MVP
gt; Peltier Technical Services
gt; Tutorials and Custom Solutions
gt; PeltierTech.com/
gt; _______
gt;
gt; quot;mustang25quot; gt; wrote in message
gt; ...
gt; gt; It amazes me to see the amount of knowledge stored on these forums. I'm
gt; gt; seriously impressed that there is an answer to my question. Thank you
gt; gt; Jon.
gt; gt;
gt; gt; Unfortunately, my VB skills are at about the same level as my 2 year old
gt; gt; son's. In other words, I don't know the first thing about it. I figured
gt; gt; out
gt; gt; how to paste the code into the VBE, but when I try to run the macro, I get
gt; gt; an
gt; gt; error saying quot;User Type not Defined.quot; I know this has to be a very basic
gt; gt; issue, but like I said, I don't know the first thing about VB. Am I
gt; gt; better
gt; gt; off just copying and pasting my charts one at a time?
gt; gt;
gt; gt; quot;Jon Peltierquot; wrote:
gt; gt;
gt; gt;gt; Partway through this article you'll find programmatic approaches to that
gt; gt;gt; very problem:
gt; gt;gt;
gt; gt;gt; peltiertech.com/Excel/XL_PPT.html
gt; gt;gt;
gt; gt;gt; - Jon
gt; gt;gt; -------
gt; gt;gt; Jon Peltier, Microsoft Excel MVP
gt; gt;gt; Peltier Technical Services
gt; gt;gt; Tutorials and Custom Solutions
gt; gt;gt; PeltierTech.com/
gt; gt;gt; _______
gt; gt;gt;
gt; gt;gt; quot;mustang25quot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; OK, I think I have a question that will stump even the best of you!
gt; gt;gt; gt;
gt; gt;gt; gt; I have several worksheets that contain multiple charts. I want to copy
gt; gt;gt; gt; the
gt; gt;gt; gt; charts from Excel to PowerPoint with one chart to a slide. I can do so
gt; gt;gt; gt; one
gt; gt;gt; gt; at a time by copying and pasting them. The drawback is that it takes
gt; gt;gt; gt; lots
gt; gt;gt; gt; of
gt; gt;gt; gt; time to do this because I'm working with 250 charts. My question is
gt; gt;gt; gt; if
gt; gt;gt; gt; there is a way to quickly export the charts to PowerPoint so that each
gt; gt;gt; gt; slide
gt; gt;gt; gt; contains a different chart.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

Jon,

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(, quot;Powerpoint.Applicationquot;)
' 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!quot;Jon Peltierquot; wrote:

gt; I suspect you didn't set the reference to PowerPoint. It's described higher
gt; on the page than the specific code you must have copied. Just under the big
gt; title quot;Activating Other Applications with Excel VBAquot;.
gt;
gt; - Jon
gt; -------
gt; Jon Peltier, Microsoft Excel MVP
gt; Peltier Technical Services
gt; Tutorials and Custom Solutions
gt; PeltierTech.com/
gt; _______
gt;
gt; quot;mustang25quot; gt; wrote in message
gt; ...
gt; gt; It amazes me to see the amount of knowledge stored on these forums. I'm
gt; gt; seriously impressed that there is an answer to my question. Thank you
gt; gt; Jon.
gt; gt;
gt; gt; Unfortunately, my VB skills are at about the same level as my 2 year old
gt; gt; son's. In other words, I don't know the first thing about it. I figured
gt; gt; out
gt; gt; how to paste the code into the VBE, but when I try to run the macro, I get
gt; gt; an
gt; gt; error saying quot;User Type not Defined.quot; I know this has to be a very basic
gt; gt; issue, but like I said, I don't know the first thing about VB. Am I
gt; gt; better
gt; gt; off just copying and pasting my charts one at a time?
gt; gt;
gt; gt; quot;Jon Peltierquot; wrote:
gt; gt;
gt; gt;gt; Partway through this article you'll find programmatic approaches to that
gt; gt;gt; very problem:
gt; gt;gt;
gt; gt;gt; peltiertech.com/Excel/XL_PPT.html
gt; gt;gt;
gt; gt;gt; - Jon
gt; gt;gt; -------
gt; gt;gt; Jon Peltier, Microsoft Excel MVP
gt; gt;gt; Peltier Technical Services
gt; gt;gt; Tutorials and Custom Solutions
gt; gt;gt; PeltierTech.com/
gt; gt;gt; _______
gt; gt;gt;
gt; gt;gt; quot;mustang25quot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; OK, I think I have a question that will stump even the best of you!
gt; gt;gt; gt;
gt; gt;gt; gt; I have several worksheets that contain multiple charts. I want to copy
gt; gt;gt; gt; the
gt; gt;gt; gt; charts from Excel to PowerPoint with one chart to a slide. I can do so
gt; gt;gt; gt; one
gt; gt;gt; gt; at a time by copying and pasting them. The drawback is that it takes
gt; gt;gt; gt; lots
gt; gt;gt; gt; of
gt; gt;gt; gt; time to do this because I'm working with 250 charts. My question is
gt; gt;gt; gt; if
gt; gt;gt; gt; there is a way to quickly export the charts to PowerPoint so that each
gt; gt;gt; gt; slide
gt; gt;gt; gt; contains a different chart.
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

In a different forum I answered one way, that you should make it in Excel so
that it comes out right in PowerPoint. I stick by that, but I'll answer the
other way as well. You can adjust this part of the code to handle resizing
of the chart.

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
PeltierTech.com
_______quot;mustang25quot; gt; wrote in message
...
gt; Jon,
gt;
gt; This is working brilliantly. I'd like to throw one more challenge at you
gt; if
gt; you don't mind. It follows below. Here is the code I'm using for the
gt; macro
gt; (you'll notice that I removed the line/command that copies the chart as a
gt; picture. This is intentional.):
gt;
gt; Sub ChartsToPresentation()
gt; ' Set a VBE reference to Microsoft PowerPoint Object Library
gt;
gt; Dim PPApp As PowerPoint.Application
gt; Dim PPPres As PowerPoint.Presentation
gt; Dim PPSlide As PowerPoint.Slide
gt; Dim PresentationFileName As Variant
gt; Dim SlideCount As Long
gt; Dim iCht As Integer
gt;
gt; ' Reference existing instance of PowerPoint
gt; Set PPApp = GetObject(, quot;Powerpoint.Applicationquot;)
gt; ' Reference active presentation
gt; Set PPPres = PPApp.ActivePresentation
gt; PPApp.ActiveWindow.ViewType = ppViewSlide
gt;
gt; For iCht = 1 To ActiveSheet.ChartObjects.Count
gt; ' copy chart as a picture
gt; ActiveSheet.ChartObjects(iCht).Copy
gt;
gt; ' Add a new slide and paste in the chart
gt; SlideCount = PPPres.Slides.Count
gt; Set PPSlide = PPPres.Slides.Add(SlideCount 1, ppLayoutBlank)
gt; PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
gt; With PPSlide
gt; ' paste and select the chart picture
gt; .Shapes.Paste.Select
gt; ' align the chart
gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
gt; End With
gt;
gt; Next
gt;
gt; ' Clean up
gt; Set PPSlide = Nothing
gt; Set PPPres = Nothing
gt; Set PPApp = Nothing
gt;
gt; End Sub
gt;
gt; Is there a way to output the charts to PowerPoint with the following
gt; parameters:
gt;
gt; Height 5.66 inches
gt; Width 9.66 inches
gt; Horizontal Position 0 inches from top left corner
gt; Vertical Position 1 inch from top left corner
gt;
gt; Many thanks in advance!
gt;
gt;
gt; quot;Jon Peltierquot; wrote:
gt;
gt;gt; I suspect you didn't set the reference to PowerPoint. It's described
gt;gt; higher
gt;gt; on the page than the specific code you must have copied. Just under the
gt;gt; big
gt;gt; title quot;Activating Other Applications with Excel VBAquot;.
gt;gt;
gt;gt; - Jon
gt;gt; -------
gt;gt; Jon Peltier, Microsoft Excel MVP
gt;gt; Peltier Technical Services
gt;gt; Tutorials and Custom Solutions
gt;gt; PeltierTech.com/
gt;gt; _______
gt;gt;
gt;gt; quot;mustang25quot; gt; wrote in message
gt;gt; ...
gt;gt; gt; It amazes me to see the amount of knowledge stored on these forums.
gt;gt; gt; I'm
gt;gt; gt; seriously impressed that there is an answer to my question. Thank you
gt;gt; gt; Jon.
gt;gt; gt;
gt;gt; gt; Unfortunately, my VB skills are at about the same level as my 2 year
gt;gt; gt; old
gt;gt; gt; son's. In other words, I don't know the first thing about it. I
gt;gt; gt; figured
gt;gt; gt; out
gt;gt; gt; how to paste the code into the VBE, but when I try to run the macro, I
gt;gt; gt; get
gt;gt; gt; an
gt;gt; gt; error saying quot;User Type not Defined.quot; I know this has to be a very
gt;gt; gt; basic
gt;gt; gt; issue, but like I said, I don't know the first thing about VB. Am I
gt;gt; gt; better
gt;gt; gt; off just copying and pasting my charts one at a time?
gt;gt; gt;
gt;gt; gt; quot;Jon Peltierquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Partway through this article you'll find programmatic approaches to
gt;gt; gt;gt; that
gt;gt; gt;gt; very problem:
gt;gt; gt;gt;
gt;gt; gt;gt; peltiertech.com/Excel/XL_PPT.html
gt;gt; gt;gt;
gt;gt; gt;gt; - Jon
gt;gt; gt;gt; -------
gt;gt; gt;gt; Jon Peltier, Microsoft Excel MVP
gt;gt; gt;gt; Peltier Technical Services
gt;gt; gt;gt; Tutorials and Custom Solutions
gt;gt; gt;gt; PeltierTech.com/
gt;gt; gt;gt; _______
gt;gt; gt;gt;
gt;gt; gt;gt; quot;mustang25quot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; OK, I think I have a question that will stump even the best of you!
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I have several worksheets that contain multiple charts. I want to
gt;gt; gt;gt; gt; copy
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; charts from Excel to PowerPoint with one chart to a slide. I can do
gt;gt; gt;gt; gt; so
gt;gt; gt;gt; gt; one
gt;gt; gt;gt; gt; at a time by copying and pasting them. The drawback is that it
gt;gt; gt;gt; gt; takes
gt;gt; gt;gt; gt; lots
gt;gt; gt;gt; gt; of
gt;gt; gt;gt; gt; time to do this because I'm working with 250 charts. My question
gt;gt; gt;gt; gt; is
gt;gt; gt;gt; gt; if
gt;gt; gt;gt; gt; there is a way to quickly export the charts to PowerPoint so that
gt;gt; gt;gt; gt; each
gt;gt; gt;gt; gt; slide
gt;gt; gt;gt; gt; contains a different chart.
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
Jon,

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.

quot;Jon Peltierquot; wrote:

gt; In a different forum I answered one way, that you should make it in Excel so
gt; that it comes out right in PowerPoint. I stick by that, but I'll answer the
gt; other way as well. You can adjust this part of the code to handle resizing
gt; of the chart.
gt;
gt; With PPSlide
gt; ' paste and select the chart picture
gt; .Shapes.Paste.Select
gt; ' align the chart
gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
gt; End With
gt;
gt; Instead of centering the chart, position and resize it:
gt;
gt; With PPSlide
gt; ' paste and select the chart picture
gt; .Shapes.Paste.Select
gt; ' position the chart
gt; With PPApp.ActiveWindow.Selection.ShapeRange
gt; .Top = 72 ' points
gt; .Left = 0
gt; .Width = 9.66 * 72
gt; .Height = 5.66 * 72
gt; End With
gt; End With
gt;
gt; - Jon
gt; -------
gt; Jon Peltier, Microsoft Excel MVP
gt; Tutorials and Custom Solutions
gt; PeltierTech.com
gt; _______
gt;
gt;
gt; quot;mustang25quot; gt; wrote in message
gt; ...
gt; gt; Jon,
gt; gt;
gt; gt; This is working brilliantly. I'd like to throw one more challenge at you
gt; gt; if
gt; gt; you don't mind. It follows below. Here is the code I'm using for the
gt; gt; macro
gt; gt; (you'll notice that I removed the line/command that copies the chart as a
gt; gt; picture. This is intentional.):
gt; gt;
gt; gt; Sub ChartsToPresentation()
gt; gt; ' Set a VBE reference to Microsoft PowerPoint Object Library
gt; gt;
gt; gt; Dim PPApp As PowerPoint.Application
gt; gt; Dim PPPres As PowerPoint.Presentation
gt; gt; Dim PPSlide As PowerPoint.Slide
gt; gt; Dim PresentationFileName As Variant
gt; gt; Dim SlideCount As Long
gt; gt; Dim iCht As Integer
gt; gt;
gt; gt; ' Reference existing instance of PowerPoint
gt; gt; Set PPApp = GetObject(, quot;Powerpoint.Applicationquot;)
gt; gt; ' Reference active presentation
gt; gt; Set PPPres = PPApp.ActivePresentation
gt; gt; PPApp.ActiveWindow.ViewType = ppViewSlide
gt; gt;
gt; gt; For iCht = 1 To ActiveSheet.ChartObjects.Count
gt; gt; ' copy chart as a picture
gt; gt; ActiveSheet.ChartObjects(iCht).Copy
gt; gt;
gt; gt; ' Add a new slide and paste in the chart
gt; gt; SlideCount = PPPres.Slides.Count
gt; gt; Set PPSlide = PPPres.Slides.Add(SlideCount 1, ppLayoutBlank)
gt; gt; PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
gt; gt; With PPSlide
gt; gt; ' paste and select the chart picture
gt; gt; .Shapes.Paste.Select
gt; gt; ' align the chart
gt; gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
gt; gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
gt; gt; End With
gt; gt;
gt; gt; Next
gt; gt;
gt; gt; ' Clean up
gt; gt; Set PPSlide = Nothing
gt; gt; Set PPPres = Nothing
gt; gt; Set PPApp = Nothing
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; Is there a way to output the charts to PowerPoint with the following
gt; gt; parameters:
gt; gt;
gt; gt; Height 5.66 inches
gt; gt; Width 9.66 inches
gt; gt; Horizontal Position 0 inches from top left corner
gt; gt; Vertical Position 1 inch from top left corner
gt; gt;
gt; gt; Many thanks in advance!
gt; gt;
gt; gt;
gt; gt; quot;Jon Peltierquot; wrote:
gt; gt;
gt; gt;gt; I suspect you didn't set the reference to PowerPoint. It's described
gt; gt;gt; higher
gt; gt;gt; on the page than the specific code you must have copied. Just under the
gt; gt;gt; big
gt; gt;gt; title quot;Activating Other Applications with Excel VBAquot;.
gt; gt;gt;
gt; gt;gt; - Jon
gt; gt;gt; -------
gt; gt;gt; Jon Peltier, Microsoft Excel MVP
gt; gt;gt; Peltier Technical Services
gt; gt;gt; Tutorials and Custom Solutions
gt; gt;gt; PeltierTech.com/
gt; gt;gt; _______
gt; gt;gt;
gt; gt;gt; quot;mustang25quot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; It amazes me to see the amount of knowledge stored on these forums.
gt; gt;gt; gt; I'm
gt; gt;gt; gt; seriously impressed that there is an answer to my question. Thank you
gt; gt;gt; gt; Jon.
gt; gt;gt; gt;
gt; gt;gt; gt; Unfortunately, my VB skills are at about the same level as my 2 year
gt; gt;gt; gt; old
gt; gt;gt; gt; son's. In other words, I don't know the first thing about it. I
gt; gt;gt; gt; figured
gt; gt;gt; gt; out
gt; gt;gt; gt; how to paste the code into the VBE, but when I try to run the macro, I
gt; gt;gt; gt; get
gt; gt;gt; gt; an
gt; gt;gt; gt; error saying quot;User Type not Defined.quot; I know this has to be a very
gt; gt;gt; gt; basic
gt; gt;gt; gt; issue, but like I said, I don't know the first thing about VB. Am I
gt; gt;gt; gt; better
gt; gt;gt; gt; off just copying and pasting my charts one at a time?
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Jon Peltierquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; Partway through this article you'll find programmatic approaches to
gt; gt;gt; gt;gt; that
gt; gt;gt; gt;gt; very problem:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; peltiertech.com/Excel/XL_PPT.html
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; - Jon
gt; gt;gt; gt;gt; -------
gt; gt;gt; gt;gt; Jon Peltier, Microsoft Excel MVP
gt; gt;gt; gt;gt; Peltier Technical Services
gt; gt;gt; gt;gt; Tutorials and Custom Solutions
gt; gt;gt; gt;gt; PeltierTech.com/
gt; gt;gt; gt;gt; _______
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;mustang25quot; gt; wrote in message
gt; gt;gt; gt;gt; ...
gt; gt;gt; gt;gt; gt; OK, I think I have a question that will stump even the best of you!
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; I have several worksheets that contain multiple charts. I want to
gt; gt;gt; gt;gt; gt; copy
gt; gt;gt; gt;gt; gt; the
gt; gt;gt; gt;gt; gt; charts from Excel to PowerPoint with one chart to a slide. I can do
gt; gt;gt; gt;gt; gt; so
gt; gt;gt; gt;gt; gt; one
gt; gt;gt; gt;gt; gt; at a time by copying and pasting them. The drawback is that it
gt; gt;gt; gt;gt; gt; takes
gt; gt;gt; gt;gt; gt; lots
gt; gt;gt; gt;gt; gt; of
gt; gt;gt; gt;gt; gt; time to do this because I'm working with 250 charts. My question
gt; gt;gt; gt;gt; gt; is
gt; gt;gt; gt;gt; gt; if
gt; gt;gt; gt;gt; gt; there is a way to quickly export the charts to PowerPoint so that
gt; gt;gt; gt;gt; gt; each
gt; gt;gt; gt;gt; gt; slide
gt; gt;gt; gt;gt; gt; contains a different chart.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

I first got into programming in order to save myself those hours of work.
Tedious redundant work. Laziness is the other of innovation.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
PeltierTech.com
_______quot;mustang25quot; gt; wrote in message
...
gt; Jon,
gt;
gt; I see what you meant in the other forum. You are right, the best way to
gt; approach this would be to have the charts sized properly in Excel to begin
gt; with. Be that as it may, your macro has already saved me HOURS of work in
gt; copying each chart to its own slide. I can deal with resizing them
gt; manually
gt; for now and will remember to size them properly in Excel the next time I
gt; do a
gt; project like this.
gt;
gt; Once again, many humble thanks.
gt;
gt; quot;Jon Peltierquot; wrote:
gt;
gt;gt; In a different forum I answered one way, that you should make it in Excel
gt;gt; so
gt;gt; that it comes out right in PowerPoint. I stick by that, but I'll answer
gt;gt; the
gt;gt; other way as well. You can adjust this part of the code to handle
gt;gt; resizing
gt;gt; of the chart.
gt;gt;
gt;gt; With PPSlide
gt;gt; ' paste and select the chart picture
gt;gt; .Shapes.Paste.Select
gt;gt; ' align the chart
gt;gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters,
gt;gt; True
gt;gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles,
gt;gt; True
gt;gt; End With
gt;gt;
gt;gt; Instead of centering the chart, position and resize it:
gt;gt;
gt;gt; With PPSlide
gt;gt; ' paste and select the chart picture
gt;gt; .Shapes.Paste.Select
gt;gt; ' position the chart
gt;gt; With PPApp.ActiveWindow.Selection.ShapeRange
gt;gt; .Top = 72 ' points
gt;gt; .Left = 0
gt;gt; .Width = 9.66 * 72
gt;gt; .Height = 5.66 * 72
gt;gt; End With
gt;gt; End With
gt;gt;
gt;gt; - Jon
gt;gt; -------
gt;gt; Jon Peltier, Microsoft Excel MVP
gt;gt; Tutorials and Custom Solutions
gt;gt; PeltierTech.com
gt;gt; _______
gt;gt;
gt;gt;
gt;gt; quot;mustang25quot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Jon,
gt;gt; gt;
gt;gt; gt; This is working brilliantly. I'd like to throw one more challenge at
gt;gt; gt; you
gt;gt; gt; if
gt;gt; gt; you don't mind. It follows below. Here is the code I'm using for the
gt;gt; gt; macro
gt;gt; gt; (you'll notice that I removed the line/command that copies the chart as
gt;gt; gt; a
gt;gt; gt; picture. This is intentional.):
gt;gt; gt;
gt;gt; gt; Sub ChartsToPresentation()
gt;gt; gt; ' Set a VBE reference to Microsoft PowerPoint Object Library
gt;gt; gt;
gt;gt; gt; Dim PPApp As PowerPoint.Application
gt;gt; gt; Dim PPPres As PowerPoint.Presentation
gt;gt; gt; Dim PPSlide As PowerPoint.Slide
gt;gt; gt; Dim PresentationFileName As Variant
gt;gt; gt; Dim SlideCount As Long
gt;gt; gt; Dim iCht As Integer
gt;gt; gt;
gt;gt; gt; ' Reference existing instance of PowerPoint
gt;gt; gt; Set PPApp = GetObject(, quot;Powerpoint.Applicationquot;)
gt;gt; gt; ' Reference active presentation
gt;gt; gt; Set PPPres = PPApp.ActivePresentation
gt;gt; gt; PPApp.ActiveWindow.ViewType = ppViewSlide
gt;gt; gt;
gt;gt; gt; For iCht = 1 To ActiveSheet.ChartObjects.Count
gt;gt; gt; ' copy chart as a picture
gt;gt; gt; ActiveSheet.ChartObjects(iCht).Copy
gt;gt; gt;
gt;gt; gt; ' Add a new slide and paste in the chart
gt;gt; gt; SlideCount = PPPres.Slides.Count
gt;gt; gt; Set PPSlide = PPPres.Slides.Add(SlideCount 1, ppLayoutBlank)
gt;gt; gt; PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
gt;gt; gt; With PPSlide
gt;gt; gt; ' paste and select the chart picture
gt;gt; gt; .Shapes.Paste.Select
gt;gt; gt; ' align the chart
gt;gt; gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters,
gt;gt; gt; True
gt;gt; gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles,
gt;gt; gt; True
gt;gt; gt; End With
gt;gt; gt;
gt;gt; gt; Next
gt;gt; gt;
gt;gt; gt; ' Clean up
gt;gt; gt; Set PPSlide = Nothing
gt;gt; gt; Set PPPres = Nothing
gt;gt; gt; Set PPApp = Nothing
gt;gt; gt;
gt;gt; gt; End Sub
gt;gt; gt;
gt;gt; gt; Is there a way to output the charts to PowerPoint with the following
gt;gt; gt; parameters:
gt;gt; gt;
gt;gt; gt; Height 5.66 inches
gt;gt; gt; Width 9.66 inches
gt;gt; gt; Horizontal Position 0 inches from top left corner
gt;gt; gt; Vertical Position 1 inch from top left corner
gt;gt; gt;
gt;gt; gt; Many thanks in advance!
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Jon Peltierquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; I suspect you didn't set the reference to PowerPoint. It's described
gt;gt; gt;gt; higher
gt;gt; gt;gt; on the page than the specific code you must have copied. Just under
gt;gt; gt;gt; the
gt;gt; gt;gt; big
gt;gt; gt;gt; title quot;Activating Other Applications with Excel VBAquot;.
gt;gt; gt;gt;
gt;gt; gt;gt; - Jon
gt;gt; gt;gt; -------
gt;gt; gt;gt; Jon Peltier, Microsoft Excel MVP
gt;gt; gt;gt; Peltier Technical Services
gt;gt; gt;gt; Tutorials and Custom Solutions
gt;gt; gt;gt; PeltierTech.com/
gt;gt; gt;gt; _______
gt;gt; gt;gt;
gt;gt; gt;gt; quot;mustang25quot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; It amazes me to see the amount of knowledge stored on these forums.
gt;gt; gt;gt; gt; I'm
gt;gt; gt;gt; gt; seriously impressed that there is an answer to my question. Thank
gt;gt; gt;gt; gt; you
gt;gt; gt;gt; gt; Jon.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Unfortunately, my VB skills are at about the same level as my 2 year
gt;gt; gt;gt; gt; old
gt;gt; gt;gt; gt; son's. In other words, I don't know the first thing about it. I
gt;gt; gt;gt; gt; figured
gt;gt; gt;gt; gt; out
gt;gt; gt;gt; gt; how to paste the code into the VBE, but when I try to run the macro,
gt;gt; gt;gt; gt; I
gt;gt; gt;gt; gt; get
gt;gt; gt;gt; gt; an
gt;gt; gt;gt; gt; error saying quot;User Type not Defined.quot; I know this has to be a very
gt;gt; gt;gt; gt; basic
gt;gt; gt;gt; gt; issue, but like I said, I don't know the first thing about VB. Am I
gt;gt; gt;gt; gt; better
gt;gt; gt;gt; gt; off just copying and pasting my charts one at a time?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; quot;Jon Peltierquot; wrote:
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt; Partway through this article you'll find programmatic approaches to
gt;gt; gt;gt; gt;gt; that
gt;gt; gt;gt; gt;gt; very problem:
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; peltiertech.com/Excel/XL_PPT.html
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; - Jon
gt;gt; gt;gt; gt;gt; -------
gt;gt; gt;gt; gt;gt; Jon Peltier, Microsoft Excel MVP
gt;gt; gt;gt; gt;gt; Peltier Technical Services
gt;gt; gt;gt; gt;gt; Tutorials and Custom Solutions
gt;gt; gt;gt; gt;gt; PeltierTech.com/
gt;gt; gt;gt; gt;gt; _______
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; quot;mustang25quot; gt; wrote in message
gt;gt; gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;gt; gt; OK, I think I have a question that will stump even the best of
gt;gt; gt;gt; gt;gt; gt; you!
gt;gt; gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;gt; gt; I have several worksheets that contain multiple charts. I want
gt;gt; gt;gt; gt;gt; gt; to
gt;gt; gt;gt; gt;gt; gt; copy
gt;gt; gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt;gt; gt; charts from Excel to PowerPoint with one chart to a slide. I can
gt;gt; gt;gt; gt;gt; gt; do
gt;gt; gt;gt; gt;gt; gt; so
gt;gt; gt;gt; gt;gt; gt; one
gt;gt; gt;gt; gt;gt; gt; at a time by copying and pasting them. The drawback is that it
gt;gt; gt;gt; gt;gt; gt; takes
gt;gt; gt;gt; gt;gt; gt; lots
gt;gt; gt;gt; gt;gt; gt; of
gt;gt; gt;gt; gt;gt; gt; time to do this because I'm working with 250 charts. My
gt;gt; gt;gt; gt;gt; gt; question
gt;gt; gt;gt; gt;gt; gt; is
gt;gt; gt;gt; gt;gt; gt; if
gt;gt; gt;gt; gt;gt; gt; there is a way to quickly export the charts to PowerPoint so that
gt;gt; gt;gt; gt;gt; gt; each
gt;gt; gt;gt; gt;gt; gt; slide
gt;gt; gt;gt; gt;gt; gt; contains a different chart.
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
Jon,
Is there a way to bring the chart in as a picture (enhanced metafile)? In
my case, bringing the chart in as a picitre also brings in the excel grid
lines. Guess I could turn off the gridlines, but I really need them as the
spreadsheet is a template for others and I wwant to be able to have everyone
bring in the chart to pp in the same size, location, etc, but with their data.

Thanks,

Danny

quot;Jon Peltierquot; wrote:

gt; In a different forum I answered one way, that you should make it in Excel so
gt; that it comes out right in PowerPoint. I stick by that, but I'll answer the
gt; other way as well. You can adjust this part of the code to handle resizing
gt; of the chart.
gt;
gt; With PPSlide
gt; ' paste and select the chart picture
gt; .Shapes.Paste.Select
gt; ' align the chart
gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
gt; End With
gt;
gt; Instead of centering the chart, position and resize it:
gt;
gt; With PPSlide
gt; ' paste and select the chart picture
gt; .Shapes.Paste.Select
gt; ' position the chart
gt; With PPApp.ActiveWindow.Selection.ShapeRange
gt; .Top = 72 ' points
gt; .Left = 0
gt; .Width = 9.66 * 72
gt; .Height = 5.66 * 72
gt; End With
gt; End With
gt;
gt; - Jon
gt; -------
gt; Jon Peltier, Microsoft Excel MVP
gt; Tutorials and Custom Solutions
gt; PeltierTech.com
gt; _______
gt;
gt;
gt; quot;mustang25quot; gt; wrote in message
gt; ...
gt; gt; Jon,
gt; gt;
gt; gt; This is working brilliantly. I'd like to throw one more challenge at you
gt; gt; if
gt; gt; you don't mind. It follows below. Here is the code I'm using for the
gt; gt; macro
gt; gt; (you'll notice that I removed the line/command that copies the chart as a
gt; gt; picture. This is intentional.):
gt; gt;
gt; gt; Sub ChartsToPresentation()
gt; gt; ' Set a VBE reference to Microsoft PowerPoint Object Library
gt; gt;
gt; gt; Dim PPApp As PowerPoint.Application
gt; gt; Dim PPPres As PowerPoint.Presentation
gt; gt; Dim PPSlide As PowerPoint.Slide
gt; gt; Dim PresentationFileName As Variant
gt; gt; Dim SlideCount As Long
gt; gt; Dim iCht As Integer
gt; gt;
gt; gt; ' Reference existing instance of PowerPoint
gt; gt; Set PPApp = GetObject(, quot;Powerpoint.Applicationquot;)
gt; gt; ' Reference active presentation
gt; gt; Set PPPres = PPApp.ActivePresentation
gt; gt; PPApp.ActiveWindow.ViewType = ppViewSlide
gt; gt;
gt; gt; For iCht = 1 To ActiveSheet.ChartObjects.Count
gt; gt; ' copy chart as a picture
gt; gt; ActiveSheet.ChartObjects(iCht).Copy
gt; gt;
gt; gt; ' Add a new slide and paste in the chart
gt; gt; SlideCount = PPPres.Slides.Count
gt; gt; Set PPSlide = PPPres.Slides.Add(SlideCount 1, ppLayoutBlank)
gt; gt; PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
gt; gt; With PPSlide
gt; gt; ' paste and select the chart picture
gt; gt; .Shapes.Paste.Select
gt; gt; ' align the chart
gt; gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
gt; gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
gt; gt; End With
gt; gt;
gt; gt; Next
gt; gt;
gt; gt; ' Clean up
gt; gt; Set PPSlide = Nothing
gt; gt; Set PPPres = Nothing
gt; gt; Set PPApp = Nothing
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; Is there a way to output the charts to PowerPoint with the following
gt; gt; parameters:
gt; gt;
gt; gt; Height 5.66 inches
gt; gt; Width 9.66 inches
gt; gt; Horizontal Position 0 inches from top left corner
gt; gt; Vertical Position 1 inch from top left corner
gt; gt;
gt; gt; Many thanks in advance!
gt; gt;
gt; gt;
gt; gt; quot;Jon Peltierquot; wrote:
gt; gt;
gt; gt;gt; I suspect you didn't set the reference to PowerPoint. It's described
gt; gt;gt; higher
gt; gt;gt; on the page than the specific code you must have copied. Just under the
gt; gt;gt; big
gt; gt;gt; title quot;Activating Other Applications with Excel VBAquot;.
gt; gt;gt;
gt; gt;gt; - Jon
gt; gt;gt; -------
gt; gt;gt; Jon Peltier, Microsoft Excel MVP
gt; gt;gt; Peltier Technical Services
gt; gt;gt; Tutorials and Custom Solutions
gt; gt;gt; PeltierTech.com/
gt; gt;gt; _______
gt; gt;gt;
gt; gt;gt; quot;mustang25quot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; It amazes me to see the amount of knowledge stored on these forums.
gt; gt;gt; gt; I'm
gt; gt;gt; gt; seriously impressed that there is an answer to my question. Thank you
gt; gt;gt; gt; Jon.
gt; gt;gt; gt;
gt; gt;gt; gt; Unfortunately, my VB skills are at about the same level as my 2 year
gt; gt;gt; gt; old
gt; gt;gt; gt; son's. In other words, I don't know the first thing about it. I
gt; gt;gt; gt; figured
gt; gt;gt; gt; out
gt; gt;gt; gt; how to paste the code into the VBE, but when I try to run the macro, I
gt; gt;gt; gt; get
gt; gt;gt; gt; an
gt; gt;gt; gt; error saying quot;User Type not Defined.quot; I know this has to be a very
gt; gt;gt; gt; basic
gt; gt;gt; gt; issue, but like I said, I don't know the first thing about VB. Am I
gt; gt;gt; gt; better
gt; gt;gt; gt; off just copying and pasting my charts one at a time?
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Jon Peltierquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; Partway through this article you'll find programmatic approaches to
gt; gt;gt; gt;gt; that
gt; gt;gt; gt;gt; very problem:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; peltiertech.com/Excel/XL_PPT.html
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; - Jon
gt; gt;gt; gt;gt; -------
gt; gt;gt; gt;gt; Jon Peltier, Microsoft Excel MVP
gt; gt;gt; gt;gt; Peltier Technical Services
gt; gt;gt; gt;gt; Tutorials and Custom Solutions
gt; gt;gt; gt;gt; PeltierTech.com/
gt; gt;gt; gt;gt; _______
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;mustang25quot; gt; wrote in message
gt; gt;gt; gt;gt; ...
gt; gt;gt; gt;gt; gt; OK, I think I have a question that will stump even the best of you!
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; I have several worksheets that contain multiple charts. I want to
gt; gt;gt; gt;gt; gt; copy
gt; gt;gt; gt;gt; gt; the
gt; gt;gt; gt;gt; gt; charts from Excel to PowerPoint with one chart to a slide. I can do
gt; gt;gt; gt;gt; gt; so
gt; gt;gt; gt;gt; gt; one
gt; gt;gt; gt;gt; gt; at a time by copying and pasting them. The drawback is that it
gt; gt;gt; gt;gt; gt; takes
gt; gt;gt; gt;gt; gt; lots
gt; gt;gt; gt;gt; gt; of
gt; gt;gt; gt;gt; gt; time to do this because I'm working with 250 charts. My question
gt; gt;gt; gt;gt; gt; is
gt; gt;gt; gt;gt; gt; if
gt; gt;gt; gt;gt; gt; there is a way to quickly export the charts to PowerPoint so that
gt; gt;gt; gt;gt; gt; each
gt; gt;gt; gt;gt; gt; slide
gt; gt;gt; gt;gt; gt; contains a different chart.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 software 的頭像
    software

    software

    software 發表在 痞客邦 留言(0) 人氣()