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
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