Excel 2016: PowerPoint 2016 from Excel 2016 VBA Issue

[ajay_gajree]

Hi All

I have tried to run this code in Excel 2016, it takes a table of data from Excel, opens PowerPoint, creates a slide, creates a table and then pastes the Excel Data into it. Well thats the aim of it!


It is failing on this line

Set Pslide = Ppres.Slides.Add(1, ppLayoutBlank)

The error coming up is "Object variable or with block variable not set"

Any ideas what the issue maybe? Thanks!


Full Code

Sub PPTFromExcelTemplate()
Dim Pslide  As PowerPoint.Slide
Dim Ppres   As PowerPoint.Presentation
Dim Papp    As PowerPoint.Application
Dim Pshape  As PowerPoint.Shape
Dim i As Integer, k As Integer, row_count As Integer, col_count As Integer, colwidth As Integer

Set Papp = New PowerPoint.Application
Papp.Visible = msoTrue
Set Pslide = Ppres.Slides.Add(1, ppLayoutBlank)

row_count = Cells(row_count, 1).End(xlUp).Row
col_count = Cells(1, col_count).End(xlToLeft).Column

Set Pshape = Pslide.Shapes.AddTable(row_count, col_count)

For i = 1 To row_count
    For k = 1 To col_count
        With Pshape.Table
            .Cell(i, k).Shape.TextFrame.TextRange.Text = Sheet1.Cells(i, k)
            .Cell(i, k).Shape.TextFrame.TextRange.Font.Size = 10
            colwidth = Application.WorksheetFunction.Choose(k, 100, 60, 40, 40, 40, 250)
            .Columns(k).Width = colwidth
            
            Select Case k
                Case 5
                    If i > 1 Then
                        .Cell(i, k).Shape.TextFrame.TextRange.Font.Name = "WingDings 3"
                    End If
                Case 3, 4
                    Select Case Cells(i, k)
                        Case "R"
                            .Cell(i, k).Shape.Fill.ForeColor.RGB = RGB(255, 0, 0)
                        Case "A"
                            .Cell(i, k).Shape.Fill.ForeColor.RGB = RGB(255, 153, 0)
                        Case "G"
                            .Cell(i, k).Shape.Fill.ForeColor.RGB = RGB(0, 255, 0)
                    End Select
            End Select
        End With
    Next k
Next i

Pshape.Left = 20
Pshape.Top = 100
    
End Sub

 

[Domenic]

That's because you haven't created a new presentation and assigned it to your variable Ppres. Try adding the following line marked in red...

Sub PPTFromExcelTemplate()
Dim Pslide  As PowerPoint.Slide
Dim Ppres   As PowerPoint.Presentation
Dim Papp    As PowerPoint.Application
Dim Pshape  As PowerPoint.Shape
Dim i As Integer, k As Integer, row_count As Integer, col_count As Integer, colwidth As Integer

Set Papp = New PowerPoint.Application
Papp.Visible = msoTrue

[COLOR=#ff0000]Set Ppres = Papp.Presentations.Add[/COLOR]

Set Pslide = Ppres.Slides.Add(1, ppLayoutBlank)

row_count = Cells(row_count, 1).End(xlUp).Row
col_count = Cells(1, col_count).End(xlToLeft).Column

Set Pshape = Pslide.Shapes.AddTable(row_count, col_count)

For i = 1 To row_count
    For k = 1 To col_count
        With Pshape.Table
            .Cell(i, k).Shape.TextFrame.TextRange.Text = Sheet1.Cells(i, k)
            .Cell(i, k).Shape.TextFrame.TextRange.Font.Size = 10
            colwidth = Application.WorksheetFunction.Choose(k, 100, 60, 40, 40, 40, 250)
            .Columns(k).Width = colwidth
            
            Select Case k
                Case 5
                    If i > 1 Then
                        .Cell(i, k).Shape.TextFrame.TextRange.Font.Name = "WingDings 3"
                    End If
                Case 3, 4
                    Select Case Cells(i, k)
                        Case "R"
                            .Cell(i, k).Shape.Fill.ForeColor.RGB = RGB(255, 0, 0)
                        Case "A"
                            .Cell(i, k).Shape.Fill.ForeColor.RGB = RGB(255, 153, 0)
                        Case "G"
                            .Cell(i, k).Shape.Fill.ForeColor.RGB = RGB(0, 255, 0)
                    End Select
            End Select
        End With
    Next k
Next i

Pshape.Left = 20
Pshape.Top = 100
    
End Sub

Hope this helps!

 

[ajay_gajree]

Doh! Thanks Domenic!