CODEWITHSUNDEEP
excelvbapowerpoint
Satvik K
Satvik K

Reputation: 19

How to add new slide while copying excel tables using "for" loop from Excel vba?

Dim myPresentation As Object
Dim mySlide As Object
Dim PowerPointApp As Object
Dim shp As Object
Dim MySlideArray As Variant
Dim MyRangeArray As Variant
Dim x As Long

PowerPointApp.ActiveWindow.Panes(1).Activate
Set myPresentation = PowerPointApp.ActivePresentation

MySlideArray = Array(1, 2)
MyRangeArray = Array(Worksheets("name").Range("A3:E17"), Worksheets("age").Range("A22:E37"))

For x = LBound(MySlideArray) To UBound(MySlideArray)
    MyRangeArray(x).Copy
    Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2)
    Set myPresentation = PowerPointApp.ActivePresentation.AddSlide(PowerPointApp.Slides.Count + 1, PowerPoint.PpSlideLayout.ppLayoutBlank).Select

Next x

Question 1) The error is "Object doesn't support this prop or method" just at the Count+1.select line. What is my mistake?

Question 2) If i have two ranges of cells "A1:E9" and "A11:E20" in same sheet that i want to paste in same slide, is there a way to write code which looks for non-empty cells from A1 and copies data till the last filled row and paste in powerpoint?

Apologies for the long question. Will be happy to get any answer.

Upvotes: 0

Views: 405

Answers (1)

BigBen
BigBen

Reputation: 49998

Set myPresentation = PowerPointApp.ActivePresentation.AddSlide(PowerPointApp.Slides.Count + 1, PowerPoint.PpSlideLayout.ppLayoutBlank).Select

There's a couple things wrong with this:

  1. You already Set myPresentation = PowerPointApp.ActivePresentation previously. I think you meant mySlide.
  2. PowerPointApp.ActivePresentation - use myPresentation, this is redundant.
  3. The method is Slides.AddSlide, not Application.AddSlide or Presentation.AddSlide.
  4. ...but you want Slides.Add since AddSlide takes a CustomLayout as its second parameter.
  5. PowerPointApp.Slides.Count - there's no Slides property of the application; that should be myPresentation.Slides.Count.
  6. PowerPoint.PpSlideLayout.ppLayoutBlank - note that this early-binding, while the rest of your code uses late-binding. (Dim myPresentation As Object, Dim mySlide As Object, etc.). Best to be consistent. I've laid out an early-binding approach

With those revisions:

Const ppLayoutBlank as Long = 12

With myPresentation.Slides
    Set mySlide = .Add(.Count + 1, ppLayoutBlank)
End With

Upvotes: 1

Related Questions