Macro to create PPT works when stepped through but not when run

[SOFL1]

Hi all,

If got a litte issue:

I'm trying to write a macro that copies a range from Excel into an template Powerpoint Presentation.
I can copy the range, open the PPT and add a new slide in the run mode without any problems.

On the second page the title will be automatically changed --> works
and below on the same page I insert the copied range --> this works as well

Now I need to bring the inserted table in the right position and therefore, I'd like to set myShape = the inserted table

I use the following code (just for this action) and it works fine when I step it through manually but when I run the code I get the following error message: "Method 'Item' of 'Shap' failed"

'the title will be set
mySlide.Shapes(1).TextFrame.TextRange = "test slide"
PowerPointApp.CommandBars.ExecuteMso ("PasteSourceFormatting")

Debug.Print mySlide.Shapes.Count
Set myShape = mySlide.Shapes(2)  'here I get the error
myShape.Left = 20
myShape.Top = 120
myShape.Height = 350
myShape.Width = 680

and on the slide are just two objects:
Table 2 --> the inserted table
Title 1 --> the title

Does anyone has an idea how the code would work when I run it? Or do I have to define the Shape(Table) differently?

Best
SOFL

 

[Domenic]

Try replacing..

Set myShape = mySlide.Shapes(2)  'here I get the error

with

With mySlide
    Set myShape = .Shapes(.Shapes.Count)  'here I get the error
End With

Hope this helps!

 

[SOFL1]

Try replacing..

Set myShape = mySlide.Shapes(2)  'here I get the error

with

With mySlide
    Set myShape = .Shapes(.Shapes.Count)  'here I get the error
End With

Hope this helps!

Thanks for your response!

If I change this it counts the shapes = 2 which is good but takes the title and not the second shape as myShape.

How can I specify that I want the second shape as MyShape?

Best
SOFL

 

[Domenic]

With mySlide
Set myShape = .Shapes(.Shapes.Count)
End With

With the above code, myShape will refer to the last pasted shape. Isn't this what you want? If not, can you please clarify?

 

[SOFL1]

Hi Domenic

Yes I want to set MyShape to the last inserted shape in order to place them on the slide with the following code:

MyShape.Left = 20
MyShape.Height = 250

However, if I try it with your code it only works when I go through the macro step by step. But when I run the macro it doesn‘t MyShape to the inserted table instead it sets it to the title which is automatically on the slide...

I hope I expressed myself understandable...

Best
SOFL

 

[Domenic]

Try...

PowerPointApp.CommandBars.ExecuteMso ("PasteSourceFormatting")

EndTime = Timer + 3 'second pause
Do Until Timer > EndTime
    DoEvents
Loop

With mySlide
    Set myShape = .Shapes(.Shapes.Count)
End With

myShape.Left = 20
myShape.Top = 120
myShape.Height = 350
myShape.Width = 680

Note that you can declare EndTime as a Single...

Dim EndTime As Single

Hope this helps!

 

[SOFL1]

THANKS A MILLION!!! I tried it on a sample data sheet at home and will implement it tomorrow

Could you quickly explain what the EndTimer and Do Until Loop is for?
Is this to give more time to the computer to calculate in the background?

Try...

EndTime = Timer + 3 'second pause
Do Until Timer > EndTime
    DoEvents
Loop

Many thanks
SOFL

 

[Domenic]

That's great, glad I could help!

And yes, that's right. It simply pauses for a few seconds to allow time for the shape to be copied to the slide.

Cheers!