Creating Powerpoint from Excel and inserting textbox fails

Question

I'm trying to create a powerpoint (with templates) from Excel (VBA) and add a textbox to every slide.

The code line, where I want to add the textbox fails with Index out of bounds/No active presentation. What is here wrong? The index of the slide should be ok - there is no change if I set the index manually.

Dim PowerPointApp As Object
Set PowerPointApp = CreateObject("PowerPoint.Application")
PowerPointApp.Visible = True


Set objP = PowerPointApp.Presentations.Add
objP.ApplyTemplate "" & Table1.Range("A1").Value & "draft.pptx"

PowerPointApp.ActivePresentation.Slides.Add 1, ppLayoutTitle

For i = 1 To 10

 objP.ApplyTemplate "" & Table2.Range("A1").Value & "template.pptx"
 PowerPointApp.ActivePresentation.Slides.Add i + 1, ppLayoutBlank
 PowerPointApp.ActivePresentation.Slides(i + 1).Select

 Table3.ChartObjects(i).CopyPicture

 PowerPointApp.ActivePresentation.Slides(i + 1).Shapes.Paste
 PowerPointApp.ActivePresentation.Slides(i + 1).Shapes(1).Top = 150
 PowerPointApp.ActivePresentation.Slides(i + 1).Shapes(1).Left = 50
 PowerPointApp.ActivePresentation.Slides(i + 1).Shapes(1).Width = 400
 PowerPointApp.ActivePresentation.Slides(i + 1).Shapes(1).Height = 300

     'Exception occurs here                            
 PowerPointApp.ActivePresentation.Slides(i + 1).Shapes.AddTextbox(msoTextOrientationHorizontal, Left:=100, Top:=100, Width:=200, Height:=50).TextFrame.TextRange.Text = "Text"
Next i

something more- your code is working fine for me after I switch off some of your local settings (like templates). But what you have showed us is incomplete code (e.g. where is beginning of With statement). There could be something outside- in templates or other snippet of your code. But I don't think so... — Kazimierz Jawor
– Kazimierz Jawor, Commented Aug 29, 2013 at 9:18
RunTime Error '-2147024809 (80070057)' Out of bounds exception Sorry - I forgot to remove the end with statement. — red
– red, Commented Aug 29, 2013 at 9:18
Try to change in the problem line this: msoTextOrientationHorizontal into 1 (yes, single number = 1) — Kazimierz Jawor
– Kazimierz Jawor, Commented Aug 29, 2013 at 9:22

Kazimierz Jawor · Accepted Answer · 2013-08-29 09:33:06Z

The problem in your situation stems from type of binding you use- late binding. In such situations some of VBA constants are not recognised and they are treated as variables.

First- if you set you VBE editor to require variable declaration mode then you would recognise that problem earlier because all three vba constants which I can find in your code would have been marked as variables:

   ppLayoutTitle
   ppLayoutBlank
   msoTextOrientationHorizontal

Second- to avoid the problem you need to convert all above constants into numbers which are:

   ppLayoutTitle    =1
   ppLayoutBlank    =12
   msoTextOrientationHorizontal    =1

in this way:

PowerPointApp.ActivePresentation.Slides.Add 1, 1 'ppLayoutTitle
PowerPointApp.ActivePresentation.Slides.Add i + 1, 12 'ppLayoutBlank
PowerPointApp.ActivePresentation.Slides(i + 1).Shapes.AddTextbox(1, Left:=100, Top:=100, Width:=200, Height:=50).TextFrame.TextRange.Text = "Text"

Third- why it was working for first of two constants? Because both were recognized as variable with value equals to 0. And in both situation 0 was accepted parameter for slide types. But 0 was not accepted value for TextBox type..

Collectives™ on Stack Overflow

Creating Powerpoint from Excel and inserting textbox fails

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related