VBA Another syntax Q (mult slides mult images pasted from XL) <long>

[Keith R]

Many thanks to Shyam Pillai for putting me on the right track with general
interoperability and getting the starter sytax to get Excel to manipulate
PP.

Now I'm stuck again, based on my lack of familiarity with PP syntax. I am
successful at pasting one image at a time to PP, but when I start adding PP
pages and additional images, it throws an error. Below is the code, and a
description of what happens- any help is greatly appreciated.

Using Office 97
TIA,
Keith

The code snippet below, when both PP2 and PP3 are true, should copy a range
from XL and paste it to a new PP slide, then (with PP3) go back to XL, grab
a different range, and paste it to a new PP slide. It will always be one
picture per slide, so the variable i should work as the index for both the
slide and the paste picture.

What actually happens is it hangs with a "runtime error
-2147024809(80070057) The index to the specified collection is out of
bounds" [XL VBA msgbox]. When I switch over to PP manually, I see that it
pasted and resized slide 1 correctly. It creates slide 2 and pastes the
second "picture" on slide 2, but the first pasted picture is actually what
is selected (handlebars visible)

I tried adding other lines, like [ .ActivePresentation.Slides(i).Select ]
just before the line that crashes, but nothing seems to be working. I also
tried [.ActiveWindow.View.GotoSlide Index:=i ] and that shows me the
second slide, but it still crashes on the same line. Also tried [
..ActiveWindow.Selection.Unselect ] in case it was a problem with the first
image still being selected, and that deselected the first image, but when I
switch to PP (on crash) neither pastes image is selected?

Lastly, I recorded a macro in PP to make sure my index assumption was ok
(second pasted image would be index of (2), but PP records using the name,
e.g. .Shapes("Picture 2") so I'm assuming that in a new PP document they
will always index in the order they are added to the presentation.

Any and all help greatly appreciated!!

<snip>
If PP2 = True Then
Set newSlide(i) = pres.Slides.Add(i, ppLayoutBlank)
Sheet14.Activate
Sheet14.Range("A1:AG47").Select
Excel.ActiveWorkbook.Sheets("V2").Range("AG47").Activate
Selection.CopyPicture
ppt.Activate
With ppt
.ActivePresentation.Slides(i).Shapes.Paste
.ActiveWindow.Selection.SlideRange.Shapes(i).Select
With .ActiveWindow.Selection.ShapeRange
.IncrementLeft 327.5
.IncrementTop 240
.ScaleWidth 0.88, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.88, msoFalse, msoScaleFromTopLeft
End With
'.ActiveWindow.Selection.Unselect 'tried this to make sure
prior selection
'wasn't
interfering
End With
i = i + 1
End If

If PP3 = True Then
Set newSlide(i) = pres.Slides.Add(i, ppLayoutBlank)
Sheet28.Activate
Sheet28.Range("A1:AG47").Select
Excel.ActiveWorkbook.Sheets("ADE").Range("AG47").Activate
Selection.CopyPicture
ppt.Activate
With ppt
'.ActiveWindow.View.GotoSlide Index:=i 'tried this to make
sure slide 2 was showing
'.ActivePresentation.Slides(i).Select 'tried this as alt
method to do same thing
.ActivePresentation.Slides(i).Shapes.Paste 'this works,
pastes image
.ActiveWindow.Selection.SlideRange.Shapes(i).Select '<--
This is the problem line
With .ActiveWindow.Selection.ShapeRange
.IncrementLeft 327.5
.IncrementTop 240
.ScaleWidth 0.88, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.88, msoFalse, msoScaleFromTopLeft
End With
End With
i = i + 1
End If
<snip>

 

[Keith R]

Figured it out- in case it helps anyone else,

apparently the picture index number is unique to the slide, so once I
changed the picture index number to 1 for each slide/image, everything
seems to be working.

Thanks
Keith

 

[Brian Reilly, MS MVP]

Keith,
Glad you figured it out. Here's an additional suggestion incase you
have more than 1 picture to deal with

dim iShapeCount as Integer

iShapeCount =
activepresentation.slides(i).shapes.count
.ActivePresentation.Slides(i).Shapes.Paste

..ActiveWindow.Selection.SlideRange.Shapes(iShapeCount+1).Select
With .ActiveWindow.Selection.ShapeRange

This gets you the number of shapes on the slide(i) before you paste.
You then work with the shape you pasted which is what the + 1 is
about. Also you wouldn't actually have to select the shape but that's
another lesson we'll hold off on at the moment.

Brian Reilly, PowerPoint MVP