Importing text from columns in Excel to specific/designated text boxes in PowerPoint?

Question

I have an Excel document with around 400 lines of data. They are sorted by column. For example, Column A is the name, B is Group, C is Location, D is Department, E is Note, and F is submitter.

I need this data to go into specific text boxes in a PowerPoint file. I found the following code:

Sub CreateSlides()
'Open the Excel workbook. Change the filename here.
Dim OWB As New Excel.Workbook
Set OWB = Excel.Application.Workbooks.Open("C:\list.xlsx")
'Grab the first Worksheet in the Workbook
Dim WS As Excel.Worksheet
Set WS = OWB.Worksheets(1)
'Loop through each used row in Column A
For i = 1 To WS.Range("A65536").End(xlUp).Row
    'Copy the first slide and paste at the end of the presentation
    ActivePresentation.Slides(1).Copy
    ActivePresentation.Slides.Paste (ActivePresentation.Slides.Count + 1)

    'Change the text of the first text box on the slide.
     ActivePresentation.Slides(ActivePresentation.Slides.Count).Shapes(1).TextFrame.TextRange.Text = WS.Cells(i, 1).Value
Next
End Sub

It created several slides (as it should have) and imported the info from Column A right to where it should have been placed.

My problem is I don't know how to get the rest of the text to populate where it should.

Answer 1

Assuming you want to pass each Excel row to a separate PPT slide in the same format, you can create a one slide PPT with textboxes or tables that can be populated via Excel VBA. Once the PPT slide is created, you can get the name or cell address (for textboxes or tables respectively) via the VBA editor in PPT by using the Immediate window to get the object name. Select the first object in your slide then type the following in the PPT Immediate window and press return.

?activewindow.Selection.shaperange.Name

What I have often done is assign names to shapes by selecting the shape and typing the name I want to call it. Then I switch over to the immediate window with the shape still selected and type:

activewindow.Selection.shaperange.Name=activewindow.Selection.shaperange.TextFrame.TextRange.Text

Note NO question mark precedes this because you are assigning a shape's name not asking what it's name is.

Finally, in the Excel Program code I assign the actual value by using a statement like the below:

aSlide.Shapes("MyShapeName").TextFrame.TextRange.Text = _
              aCell.Offset(0, iCol).Value

Below is a recent example of loading 100 slides from Excel VBA

Private Sub PPTLoad()
  Dim PPT As PowerPoint.Application
  Dim PPTPres As Presentation
  Dim PPTFIleName As Variant
  Dim iCnt As Integer
  Dim aSlide As PowerPoint.SlideRange

  Dim sWS As Worksheet
  Dim aCell As Range
  Dim lRow As Long
  Dim sld As Slide
  Dim iCol As Integer
  Set sWS = ThisWorkbook.Sheets("vwRawData")
  lRow = sWS.UsedRange.Rows.Count


  Set PPT = New PowerPoint.Application
  PPT.Visible = True
  PPTFIleName = FileBrowse("*.ppt", "Open PowerPoint Template")
  If PPTFIleName = "" Then
    Exit Sub
  End If

  Set PPTPres = PPT.Presentations.Open(Filename:=PPTFIleName)
  With PPTPres

    For Each aCell In sWS.Range("A3:A" & lRow)

        For iCnt = 1 To aSlide.Shapes.Count

            iCol = 2
            Select Case aSlide.Shapes(iCnt).Name
              Case "BizGroup"           'A
                 iCol = 0
                 aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  aCell.Offset(0, iCol).Value
              Case "Division"           'B
                 iCol = 1
                 aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  aCell.Offset(0, iCol).Value
              Case "NPPPjtNbr"          'D
                 iCol = 3
                 aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  aCell.Offset(0, iCol).Value
              Case "PgmName"            'E
                 iCol = 4
                 aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  aCell.Offset(0, iCol).Value
              Case "Description"        'F
                 iCol = 5
                 aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  aCell.Offset(0, iCol).Value
              Case "ValueProposition"   'G
                 iCol = 6
                 aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  aCell.Offset(0, iCol).Value
              Case "NPICurrentPhase"    'H
                 iCol = 7
                 aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  aCell.Offset(0, iCol).Value
              Case "LaunchDate"         'I
                iCol = 8
                aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  Format(aCell.Offset(0, iCol).Value, "mmm-yy")
              Case "Class"              'J
                 iCol = 9
                 aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  aCell.Offset(0, iCol).Value
              Case "SalesYTDCY"         'K
                iCol = 10
                aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  Format(aCell.Offset(0, iCol).Value / 1000000, "$#.0M")
              Case "OPPlanCY"           'L
                iCol = 11
                aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  Format(aCell.Offset(0, iCol).Value / 1000000, "$#.0M")
              Case "CYPlus1Forecast"    'M
                iCol = 12
                aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  Format(aCell.Offset(0, iCol).Value / 1000000, "$#.0M")
              Case "CYPlus2Forecast"    'N
                iCol = 13
                aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  Format(aCell.Offset(0, iCol).Value / 1000000, "$#.0M")
              Case "SalesAtMaturity"    'O
                iCol = 14
                aSlide.Shapes(iCnt).TextFrame.TextRange.Text = _
                  Format(aCell.Offset(0, iCol).Value / 1000000, "$#.0M")
              Case "OrigLaunchTime"     '?
                'iCol = 14
              Case "LaunchPlanInHome"   '
                'icol = 15
              Case "LaunchPlanInOther"  '
              Case "SalesPriorYR"       '
            End Select
        Next iCnt   '*** For Each aShape In aSlide.Shapes
    Next aCell

  End With
End Sub