Excel to existing powerpoint using VBA

[gigantedemierda]

Hi, I am new to VBA and I´m trying to fill existing powerpoint presentations with graphs and charts from a workbook.

The workbook has several sheets, which were created in both english and spanish office (I am using Office 2013, but the file may be from an older one). So, sheets have their name, but some appear as "Hoja X" and some as "Sheet Y". Just 1 and 3 are repeated (i.e. Hoja 1, Hoja 3, Sheet 1 and Sheet 3 appear in the workbook.)

(Here's a pic, to make things clearer.)

The power point has several slides, and sometimes I need to put more than one graph per slide. Thus, I need to resize and position the stuff I'm copying into the ppt.

This is what I have for just one slide:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub ChartToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
' Reference existing instance of PowerPoint
Set PPApp = GetObject(,"Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

' 6 - Convocatoria - Presentismo
Set PPSlide = PPPres.Slides(6)
ThisWorkbook.Worksheets("FyV").ChartObjects(15).Select
'Hoja8.ChartObjects(15).Select
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
PPSlide.Shapes.Paste
PPApp.ActiveWindow.Selection.ShapeRange.Left =10
PPApp.ActiveWindow.Selection.ShapeRange.Top =20
'PPSlide.ShapeRange.Width = 80
'PPSlide.ShapeRange.Height = 80
</code>EndSub

However, I´m having several problems due to the fact that I´m selecting stuff.

1. When I ask for
   <code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;"> ThisWorkbook.Worksheets("FyV").ChartObjects(15).Select</code>I get chart 24 from that worksheet. When I ask for charts 3, 12 and 13 I get chart 5.
   1. When I uncomment
      <code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">'PPSlide.ShapeRange.Width = 80
      'PPSlide.ShapeRange.Height = 80</code>
   I get the following error:
   Compile error: Method or data member not found
   ​
   1. Sometimes the line:
      <code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">ThisWorkbook.Worksheets("FyV").ChartObjects(XX).Select</code>
   Gets the following error:
   Run-time error '1004': Application-defined or object-defined error
   ​
   But XX exists, and it is on "FyV"

I now know that because I´m selecting it only picks graphs from the active sheet and that the ChartObjects(XX) is a reference to the order in which the graphs were created and not to the chart name.

So, I am looking for a way to call graphs by the name of the worksheet and graph name which allows me to resize them.

Thanks in advance,
Bauti

 

[Domenic]

Try...

<SPAN style="color:#00007F">Sub</SPAN> ChartToPresentation()<br>    <SPAN style="color:#007F00">' Set a VBE reference to Microsoft PowerPoint Object Library</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> PPApp <SPAN style="color:#00007F">As</SPAN> PowerPoint.Application<br>    <SPAN style="color:#00007F">Dim</SPAN> PPPres <SPAN style="color:#00007F">As</SPAN> PowerPoint.Presentation<br>    <SPAN style="color:#00007F">Dim</SPAN> PPSlide <SPAN style="color:#00007F">As</SPAN> PowerPoint.Slide<br>    <SPAN style="color:#00007F">Dim</SPAN> oShape <SPAN style="color:#00007F">As</SPAN> PowerPoint.Shape<br>    <SPAN style="color:#007F00">' Reference existing instance of PowerPoint</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> PPApp = GetObject(, "Powerpoint.Application")<br>    <SPAN style="color:#007F00">' Reference active presentation</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> PPPres = PPApp.ActivePresentation<br>    PPApp.ActiveWindow.ViewType = ppViewSlide<br>    <SPAN style="color:#007F00">' 6 - Convocatoria - Presentismo</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> PPSlide = PPPres.Slides(6)<br>     ThisWorkbook.Worksheets("FyV").ChartObjects("Chart 1").CopyPicture Appearance:=xlScreen, Format:=xlPicture<br>     PPSlide.Shapes.Paste<br>     <SPAN style="color:#00007F">With</SPAN> PPSlide<br>        <SPAN style="color:#00007F">Set</SPAN> oShape = .Shapes(.Shapes.Count)<br>     <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>     <SPAN style="color:#007F00">'oShape.LockAspectRatio = msoFalse</SPAN><br>     oShape.Left = 10<br>     oShape.Top = 20<br>     oShape.Width = 80<br>     oShape.Height = 80<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

Note that you may need to set the LockAspectRatio property to msoFalse. If so, uncomment the line in the code, accordingly.

Hope this helps!

 

[gigantedemierda]

Thank you very much for the quick reply. I'll try that later. Meanwhile, I made it work doing this:

Worksheets("FyV").Select


' 6 - Convocatoria - Presentismo
Set PPSlide = PPPres.Slides(6)
ThisWorkbook.Worksheets("FyV").ChartObjects("Chart 15").Select
'Hoja8.ChartObjects(15).Select
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
PPSlide.Shapes.Paste
PPApp.ActiveWindow.Selection.ShapeRange.Left = 40
PPApp.ActiveWindow.Selection.ShapeRange.Top = 200
PPApp.ActiveWindow.Selection.ShapeRange.Width = 160
PPApp.ActiveWindow.Selection.ShapeRange.Height = 160

Since there were few worksheet changes it wasn´t that hard to add the worksheet line every time there was a change.

Thanks!

 

[Domenic]

You're welcome! Thanks for the feedback!