CODEWITHSUNDEEP
excelvbapowerpoint
red
red

Reputation: 119

Creating Powerpoint from Excel and inserting textbox fails

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

Upvotes: 0

Views: 6925

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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..

Upvotes: 3

Related Questions