PDA

View Full Version : VBA Slide array defined by user in Excel



eduardodanon
11-05-2015, 09:25 AM
Hello, everybody. I have the following code to copy ranges from Excel sheets into an opened PPT.
It uses a table "tb" in Excel, with 2 columns (the first one is the sheet name and the second one is the range of the sheet to be copied) and it is working properly.
The problem is that it adds the ranges to PPT slides in sequence (2,3,4,etc) and i want the user to input the slide number INTO A THIRD COLUMN of the table "tb". (for example, add the first one to slide three, the second one to slide 10, etc)
I've tried adding these lines:

ReDim MySlideArray(.Rows.Count - 1)
Set MySlideArray(n - 1) = (.Cells(n, 1).Value)
and editing this line:

Set shp = myPresentation.Slides(x + 2).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010

for the following, without success:


Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010

How can i do that? Here is the code used so far:


On Error Resume Next
Sheets("Home").Visible = True
TotalSlides = Range("I1").Value

Set tb = Worksheets("Home").ListObjects("tblCopy")
With tb.DataBodyRange
ReDim MyRangeArray(.Rows.Count - 1)

For n = 1 To .Rows.Count
Set MyRangeArray(n - 1) = Worksheets(.Cells(n, 1).Value).Range(.Cells(n, 2).Value)

Next
End With

(...)

'Make PowerPoint Visible and Active
PowerPointApp.ActiveWindow.Panes(2).Activate

'Create a New Presentation
Set myPresentation = PowerPointApp.ActivePresentation
'Loop through Array data
For x = LBound(MyRangeArray) To UBound(MyRangeArray)
'Copy Excel Range

MyRangeArray(x).Copy

'Paste to PowerPoint and position
On Error Resume Next
Set shp = myPresentation.Slides(x + 2).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010
' Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
On Error GoTo 0