Djani
05-19-2016, 02:03 AM
Dear all,
I have a working code (thanks JonPeltier for sharing your knowledge). I have adjusted it a little, but with my knowledge I am unable to get what I am looking for.
Right now the macro is looping through all (5) charts in a specific sheet and places ALL of them in the same exact slide.
I want the macro to loop through the charts and place each individual chart in another PowerPoint slide.
This is what I have now:
Sub ChartToPPT()
' Defining variables
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim shp As String
Dim newShape As PowerPoint.ShapeRange
Dim rng As Range
Dim cell As Range
Dim chtOb As ChartObject
Dim x As Integer
'x is the number that is used by the macro to paste the chart in the desired PPT-slide
'It retrieves its number by making use of the name manager "PPTSlide"
x = Worksheets("Brand TPVA G5 Country").Range("PPTSlide") 'Name Manager in sheet "Brand TPVA G5 Country"'
'If x has no value, then it gives the message "Missing Variable"
If x = 0 Then
MsgBox "Please define combination in sheet Variable.", vbExclamation, _
"Missing Variable"
Else
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
' Reference active slide
Set PPSlide = PPPres.Slides(Worksheets("Brand TPVA G5 Country").Range("A14").Value)
' Clicks/activates the charts
For Each chtOb In ActiveSheet.ChartObjects
chtOb.Chart.ChartArea.Copy
PPSlide.Shapes.Paste.Select
Next
' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
Format:=xlPicture
' Paste chart
Set newShape = PPSlide.Shapes.Paste
' Part down-below automatically resizes the pasted chart
' Numbers can be adjusted to your own needs
With newShape
.IncrementLeft 400
.IncrementTop 300
.ScaleWidth 0.87, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.87, msoFalse, msoScaleFromTopLeft
End With
' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End If
End Sub
I want to work with chart names, such as "Chart 1", "Chart 2", "Chart 3" etc.
Each chart will have it's own name manager to define the PPT slides:
- Chart 1 will refer to (name manager) PPTSlide1
- Chart 2 will refer to PPTSlide 2
Can this be done? I need some help!
Would be greatly appreciated!
I have a working code (thanks JonPeltier for sharing your knowledge). I have adjusted it a little, but with my knowledge I am unable to get what I am looking for.
Right now the macro is looping through all (5) charts in a specific sheet and places ALL of them in the same exact slide.
I want the macro to loop through the charts and place each individual chart in another PowerPoint slide.
This is what I have now:
Sub ChartToPPT()
' Defining variables
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim shp As String
Dim newShape As PowerPoint.ShapeRange
Dim rng As Range
Dim cell As Range
Dim chtOb As ChartObject
Dim x As Integer
'x is the number that is used by the macro to paste the chart in the desired PPT-slide
'It retrieves its number by making use of the name manager "PPTSlide"
x = Worksheets("Brand TPVA G5 Country").Range("PPTSlide") 'Name Manager in sheet "Brand TPVA G5 Country"'
'If x has no value, then it gives the message "Missing Variable"
If x = 0 Then
MsgBox "Please define combination in sheet Variable.", vbExclamation, _
"Missing Variable"
Else
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
' Reference active slide
Set PPSlide = PPPres.Slides(Worksheets("Brand TPVA G5 Country").Range("A14").Value)
' Clicks/activates the charts
For Each chtOb In ActiveSheet.ChartObjects
chtOb.Chart.ChartArea.Copy
PPSlide.Shapes.Paste.Select
Next
' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
Format:=xlPicture
' Paste chart
Set newShape = PPSlide.Shapes.Paste
' Part down-below automatically resizes the pasted chart
' Numbers can be adjusted to your own needs
With newShape
.IncrementLeft 400
.IncrementTop 300
.ScaleWidth 0.87, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.87, msoFalse, msoScaleFromTopLeft
End With
' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End If
End Sub
I want to work with chart names, such as "Chart 1", "Chart 2", "Chart 3" etc.
Each chart will have it's own name manager to define the PPT slides:
- Chart 1 will refer to (name manager) PPTSlide1
- Chart 2 will refer to PPTSlide 2
Can this be done? I need some help!
Would be greatly appreciated!