Thread: Excel Cells to PPT Cells- Two Variable Ranges

Hi all,

I've attempted to write a macro that copies individual cells from Excel sheets to individual cells in tables that I have set up in PowerPoint:

1. For each second row of data in Excel, the macro creates a slide with an empty PowerPoint table in PowerPoint. (The originally empty PowerPoint tables on all slides are identical.)
2. Then it is supposed to put the data from each cell in each of those second Excel rows into the proper cell of the table on each PowerPoint slide. So, the 1st row cells are populated into the designated cells of the 1st slide, the 3rd row cells are populated into the designated cells of the 2nd slide, and so on...

I'm not sure of all the VBA options, but I've been attempting to do this primarily with For Next Loops:

- I successfully created a For Next Loop for Step 1 above. A slide has been created for each second row of data in my Excel sheet.
- However step two has two loop ranges: it must loop through each slide in the PowerPoint presentation, and at the same time it must loop through every second row of data in the Excel sheet-- because the macro populates the slides with the data of each second row of the Excel sheet. You see?

I see an option of successive or nested loops, but no option of two value ranges looping at the same time. Perhaps I shouldn't be using For Next Loops?

Sub celltocell()

Dim ppApp As PowerPoint.Application
Set ppApp = New PowerPoint.Application

Dim file As String
file = "C:\Users\ML\Desktop\Presentation1.pptx"

Dim ppPres As PowerPoint.Presentation
Set ppPres = ppApp.Presentations.Open(file)

ppApp.Visible = True
ppApp.ActiveWindow.ViewType = ppViewSlide

Dim lastrow As Integer
Dim lastcol As Integer
lastrow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).row
lastcol = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Column

Dim x As Integer
Dim q As Integer
q = lastrow / 2

For x = 1 To (q - 1)
ppApp.ActivePresentation.Slides(1).Duplicate
Next x

Dim y As Integer
Dim b As Integer

For y = 1 To (lastrow - 2) Step 2
For b = 1 To (q - 1)
Dim col1 As String
col1 = Cells(y, 1)

ppApp.ActivePresentation.Slides(b).Select
ppApp.ActiveWindow.Selection.SlideRange.Shapes("Table 1").Table.cell(1, 1).Shape.TextFrame.TextRange.Select
ppApp.ActiveWindow.Selection.TextRange.Text = col1

Dim col2 As String
col2 = Cells(y, 2)

ppApp.ActivePresentation.Slides(b).Select
ppApp.ActiveWindow.Selection.SlideRange.Shapes("Table 1").Table.cell(2, 1).Shape.TextFrame.TextRange.Select
ppApp.ActiveWindow.Selection.TextRange.Text = col2

Dim col3 As String
col3 = Cells(y, 3)

ppApp.ActivePresentation.Slides(b).Select
ppApp.ActiveWindow.Selection.SlideRange.Shapes("Table 1").Table.cell(3, 1).Shape.TextFrame.TextRange.Select
ppApp.ActiveWindow.Selection.TextRange.Text = col3

Dim col4 As String
col4 = Cells(y, 4)

ppApp.ActivePresentation.Slides(b).Select
ppApp.ActiveWindow.Selection.SlideRange.Shapes("Table 1").Table.cell(4, 1).Shape.TextFrame.TextRange.Select
ppApp.ActiveWindow.Selection.TextRange.Text = col4

Next b
Next y


End Sub

As you can see I'm having trouble finding a way to loop through b and y simultaneously, so that the 1st row of data goes on the first slide, the 3rd row of data goes on the second slide, the 5th row of data goes on the 3rd slide, etc.

If the thread title does not correctly describe the contents, please suggest how to rephrase it.

Thank you,

ML

Sub celltocell()
  sn=thisworkbook.sheets(1).cells(1).currentregion

  with Getobject("C:\Users\ML\Desktop\Presentation1.pptx")
    for j=1 to ubound(sn) 
      .Slides(1).Duplicate
    next
    for j=1 to ubound(sn)
      for jj=0 to 3
        .Slides(j).Shapes(1).Table.Cell(jj\2+1, jj mod 2 +1).Shape.TextFrame.TextRange.Text = sn(j, jj)
      next
    next
  end with
End Sub

It's a nice task for you to find out how to write in ot a certain cell in a table in PPT directly.

try separately:

.Slides(1).SlideRange.Shapes("Table 1").Table.cell(1, 1).Shape.TextFrame.TextRange.Text = "test"

if you make a reference in Excel's VBEditor to the ppt-object library you can enter this code and get the help of intellisense (automatic completion).

I improved/adapted my first suggestion using Intellisense.

in ot is a typo for 'into'

IntelliSense just helps you figure out what to write after the period, right? I use it a little, but the problem is I often don't know what to write before the period

I fear you didn't use my improved code in post #2, because that doesn't contain sliderange.
Please have a look again.
I hope you realise it's rather complicated to advise without seeing a sample powerpoint. For instance: I can't know what the table looks.like.

If you test the fist code step by step (F8 ), you can report the value of variable j and the value of variable jj. and the value of sn(j,jj)

If you wnat to see what happens

With Getobject("...

  .application.visible=true