May I Copy more than one chart from Excel , and Paste the same PPT page's specific position ?

[Prectice]

1. I want to Put more than two Chart to composition my every 1 Page PPT by VBA , how do I fulfill it ??

2. Can I copy some ranges of Excel , and paste to PPT Page as a Title ?

3. Can I copy some ranges of Excel , and paste to PPT Page , as a description ?

 

[par60056]

Welcome to the forum. I'll try to help you.

Programming PPT from VBA is not as well documented as you would hope. PPT does not have a macro recorder function so you cannot simply record what you do and the expand on it.

I have a processes that I wrote that takes snapshots of reports from Excel and pastes them into image blocks on PPT slides.

What you will need to do is set up a template of what you want the slides to look like. Title at the top, place for descriptive text places to put the charts from Excel. Then duplicate the slides and fill in the information.

I can give you some bits from my code that may help you on the way.

You need to add a reference to "Microsoft PowerPoint xx.x Object Library" Then you can define the objects to work with.

Dim ppApp As PowerPoint.Application
Dim ppPres As Presentation
Dim ppSlide As Slide
Dim ppContentSlide As Slide
Dim ppReportLayout As CustomLayout

' Create instance of PowerPoint
Set ppApp = CreateObject("Powerpoint.Application")

' Create the presentation by opening your template file
Set ppPres = ppApp.Presentations.Open(template, msoFalse)

'find the template slide you will duplicate
Set ppReportLayout = ppPres.SlideMaster.CustomLayouts(findLayout(ppPres.SlideMaster.CustomLayouts, "Chart Slide"))

'Assume this is in some loop  and you are keeping track of what slide you are on in the presentation
'this adds a new slide using the template made earlier
Set ppSlide = ppPres.Slides.AddSlide(currSlide, ppReportLayout)

'set the text value of a field (could be the title or some descriptive text )
ppSlide.Shapes("Text Placeholder 1").TextFrame2.TextRange.Text = "Report Date: " & Format(Now, "dd-mmm-yyyy")


'Get the position information on where to put the picture
ptTop = ppSlide.Shapes("Picture Placeholder 1").Top
ptLeft = ppSlide.Shapes("Picture Placeholder 1").Left
ptHeight = ppSlide.Shapes("Picture Placeholder 1").Height
ptWidth = ppSlide.Shapes("Picture Placeholder 1").Width


'Copy the information from Excel  This is copying a range of cells.  Copying a chart will be different
sh.Range(sh.Cells(4, 1), sh.Cells(rangeEnd, lastCol)).CopyPicture

'paste in the copied image and then resize to the placeholder shape
ppSlide.Shapes.Paste
ppSlide.Shapes(ppSlide.Shapes.Count).Top = ptTop
ppSlide.Shapes(ppSlide.Shapes.Count).Left = ptLeft
If (ppSlide.Shapes(ppSlide.Shapes.Count).Height > ptHeight) Then
    resizeFactor = ptHeight / ppSlide.Shapes(ppSlide.Shapes.Count).Height
    ppSlide.Shapes(ppSlide.Shapes.Count).Height = ppSlide.Shapes(ppSlide.Shapes.Count).Height * resizeFactor
End If

If (ppSlide.Shapes(ppSlide.Shapes.Count).Width > ptWidth) Then
    resizeFactor = ptWidth / ppSlide.Shapes(ppSlide.Shapes.Count).Width
    ppSlide.Shapes(ppSlide.Shapes.Count).Width = ppSlide.Shapes(ppSlide.Shapes.Count).Width * resizeFactor
End If
ppSlide.Shapes("Picture Placeholder 1").Delete

I can be done but it will be a challenge. Hope this helps.

 

[Prectice]

Quite appreciate for your help , I will try to fulfill it from your code . And I have follow question , want to call your help , that is :


1. Can I copy a Old Files as another New File , and rename the New File as Template , and Choose certain page of PPT , to start up and End of Some page of PPT , by VBA?
a. Old File , can't be change content , and close
b. New File , have to inherit the Old file's Feature , Such as format setting
c. I only want to modify some page of PPT , Such like : Page 7~ Page 14
d. At Certain Page , Such as Page 7 , I want to delete old chart ( more than one ) , and replace to new one ( new chart come from the excel paste to , by VBA code we discuss above ) <--- how to fulfill it ??


2. Can I , change the format of PPT Template , by VBA ?
-> such as , A format to B format


3. Can I , Change the Words or slide content of Old PPT , by VBA ?


4. Is it possible , to trigger this macro up , at some time automatically, by VBA ?
-> Such as 5:00 AM , everyday

 

[par60056]

Quite appreciate for your help , I will try to fulfill it from your code . And I have follow question , want to call your help , that is :


1. Can I copy a Old Files as another New File , and rename the New File as Template , and Choose certain page of PPT , to start up and End of Some page of PPT , by VBA?
a. Old File , can't be change content , and close
b. New File , have to inherit the Old file's Feature , Such as format setting
c. I only want to modify some page of PPT , Such like : Page 7~ Page 14
d. At Certain Page , Such as Page 7 , I want to delete old chart ( more than one ) , and replace to new one ( new chart come from the excel paste to , by VBA code we discuss above ) <--- how to fulfill it ??


2. Can I , change the format of PPT Template , by VBA ?
-> such as , A format to B format


3. Can I , Change the Words or slide content of Old PPT , by VBA ?


4. Is it possible , to trigger this macro up , at some time automatically, by VBA ?
-> Such as 5:00 AM , everyday

1) Here is what I do in my process. It is very similar to what you want to do.
My template file has 6 slides in it plus some slides that are defined as templates. Template slides are slides that can be used to create a new slide.

I open the "Template.ppt" change some of the text on the 1st slide to show what is really going to be in the file, delete 1 slide I don't want, then add slides to the end using 1 of 3 templates depending on what is being copied from Excel.

It is probably easier to insert a new slide and delete the old one than to try to remove a chart and replace it. I'm sure that it can be done. They are just objects with height, width and location. So you would want to get the area of the old one, then delete it and insert the new image.

When you have finished SaveAs to a new name. don't overwrite the template. Keep the master copy intact.

2 & 3) You can do pretty much anything. It takes some work to figure it out since you can't macro record. You can change text in a text object or change the font or size. You have a lot of control.

4) No idea on automatic triggering. Sorry

 

[ciprianmp]

Hello all,

Can anyone show me how to paste 2 different ranges as pictures, one under each other?
The picture I have is too long to fit on the length of a slide (it becomes unreadable), so I want to split it in 2 rows (2 pictures one under each other, probably different widths).
How that can be achieved?
Thank you in advance!