CODEWITHSUNDEEP
excelvbapowerpoint
Satvik K
Satvik K

Reputation: 19

.Automation Error. Unable to copy array of data into powerpoint slides from excel worksheets

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

Set myPresentation = PowerPointApp.ActivePresentation

  MySlideArray = Array(1, 2, 3, 4)

  MyRangeArray = Array(Worksheets("Name File").Range("A1:C10"), _
                       Worksheets("Age File").Range("A1:C10"), _
                       Worksheets("Location File").Range("A1:C10"), _
                       Worksheets("DOB File").Range("A1:C10"))

  For x = LBound(MySlideArray) To UBound(MySlideArray)
        MyRangeArray(x).Copy
        Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange
  Next x

  Application.CutCopyMode = False
  ThisWorkbook.Activate

On debugging, sometimes the code runs all the way but only the first range gets copied to powerpoint, while rest are not. Othertimes, i get a run-time error while using ShapeRange (pasting). Where did i do wrong? Please help. Learning VBA since 1 week. Error looks like this

Upvotes: 0

Views: 70

Answers (1)

Domenic
Domenic

Reputation: 8114

It looks like you want to copy each range referenced in MyRangeArray and past them into their respective slide referenced in MySlideArray.

The following line of code will paste each range into their respective slide, and returns a ShapeRange.

Set shp = myPresentation.slides(MySlideArray(x)).Shapes.Paste

To return a Shape object instead, try the following...

Set shp = myPresentation.slides(MySlideArray(x)).Shape`s.Paste(1)

By the way, myPresentation, mySlide, and PowerPointApp are declared as Variant, since you haven't specified a type. To declare them as Object, you'll need to specify the type as follows...

Dim myPresentation as Object, mySlide as Object, PowerPointApp as Object, shp As Object

And, the same thing applies to your next declaration line.

Upvotes: 1

Related Questions