Forum Discussion

JmsBvr's avatar
JmsBvr
Copper Contributor
Jun 18, 2021

Powerpoint Macro Help

Hello all!  I am working with a macro that will pull data from an active Excel sheet and create slides based on info in the sheet.  Currently, I am able to create the correct number of slides, and Cell 1 and Cell 2 both carry over to the slides perfectly.  What I would like to do is carry over more than just those 2.  I will paste the macro here for your reference:

 

Sub CreateSlidesTest_Text2()
'https://www.contextures.com
'create slide for names
' that pass criteria test
'fill two text boxes
Dim myPT As Presentation
Dim myMain As Slide
Dim myDup As Slide

Dim xlApp As Object
Dim wbA As Object
Dim wsA As Object
Dim myList As Object
Dim myRng As Object
Dim i As Long
Dim col01 As Long
Dim col02 As Long
Dim colTest As Long
Dim strTest As String

'columns with text for slides
col01 = 1
col02 = 2
'test column and criterion
colTest = 3
strTest = "y"

On Error Resume Next
Set myPT = ActivePresentation
Set myMain = myPT.Slides(1)

Set xlApp = GetObject(, "Excel.Application")
Set wbA = xlApp.ActiveWorkbook
Set wsA = wbA.ActiveSheet
Set myList = wsA.ListObjects(1)
On Error GoTo errHandler

If Not myList Is Nothing Then
  
  Set myRng = myList.DataBodyRange
  
  For i = 1 To myRng.Rows.Count
    'Copy first slide, paste after last slide
    If UCase(wsA.Cells(i, colTest).Value) _
        = UCase(strTest) Then
      With myPT
        'Duplicate slide 1, move after last slide
        myMain.Duplicate
        Set myDup = .Slides(2)
        myDup.MoveTo myPT.Slides.Count
        
        'change text in 1st textbox
        myDup.Shapes(1).TextFrame.TextRange.Text _
            = myRng.Cells(i, col01).Value
         
         'change text in 2nd textbox
        myDup.Shapes(2).TextFrame.TextRange.Text _
            = myRng.Cells(i, col02).Value
      End With
    End If
  Next
Else
  MsgBox "No Excel table found on active sheet"
  GoTo exitHandler
End If

exitHandler:
  Exit Sub
errHandler:
  MsgBox "Could not complete slides"
  Resume exitHandler:
End Sub

Credit for the macro here:

https://www.contextures.com/excelpowerpointslideslist.html

 

I added a

Dim col03 As Long

and

col03 = 7

and

'change text in 3rd textbox
myDup.Shapes(3).TextFrame.TextRange.Text _
= myRng.Cells(i, col03).Value

What am I missing?  I would like to pull around 5 - 8 cells from the excel sheet to the slides, eventually.

 

THANKS!

No RepliesBe the first to reply