ELSUIS
12-14-2017, 01:37 AM
I've written a piece of code to copy data from excel and paste it in a powerpoint presentation.
I constantly get the following error:
Selection.ShapeRange : Invalid Request. Nothing Appropriate is currently selected
Which relates to the following part of the code (where I am pasting the Excel data to the powerpoint slide and determine its position).
PPSlide.Shapes.PasteSpecial DataType:=2
pp.ActiveWindow.Selection.ShapeRange.Top = 0
pp.ActiveWindow.Selection.ShapeRange.Left = 0
pp.ActiveWindow.Selection.ShapeRange.Width = 1000
The weird thing is that this code used to work a couple of weeks ago (in excel 2016), but since today (I got downgraded to Excel 2010) it suddenly stopped working..
The full code I am using is as follows:
'Step 1: Declare variables
Dim pp As Object
Dim PPPres As Object
Dim PPSlide As Object
Dim xlwksht As Worksheet
Dim MyRange As String
Dim MyRange2 As String
Dim TemplatePath As String
'Step 2: Open PowerPoint, add a new presentation and make visible
Set pp = CreateObject("PowerPoint.Application")
Set PPPres = pp.Presentations.Add
pp.Visible = True
'Step 3: Start the loop through each worksheet
'Step 3-A: Skip Excel sheets 1 till 8
For Each xlwksht In Worksheets
If xlwksht.Index >= 9 Then
'Step 3-B: Count slides and add new blank slide as next available slide number
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
PPSlide.Select
'Step 4: Copy the Content section from Excel
MyRange = xlwksht.Range("A1").Value & ":" & xlwksht.Range("A2").Value
xlwksht.Range(MyRange).Copy
'Step 5: Paste the Content and adjust its position
'Step 5-A: Determine the Path of the Template and apply it to the Powerpoint presentation
PPPres.ApplyTemplate (TemplatePath & "\Template.potx")
'Step 5-B: Determine the PasteType
pastetype = xlwksht.Range("C1").Value 'Where C1 = "Image" for all images and tables
PasteWidth = xlwksht.Range("D1").Value 'Where D1 = "Title" then picture will fill whole screen
'Step 5-C: Based on the Pastetype paste the content in the presentation
If pastetype = "Image" Then
If PasteWidth = "Title" Then
'Step 5-C-1 Format only for Title Page
PPSlide.Shapes.PasteSpecial DataType:=2
pp.ActiveWindow.Selection.ShapeRange.Top = 0
pp.ActiveWindow.Selection.ShapeRange.Left = 0
pp.ActiveWindow.Selection.ShapeRange.Width = 1000
Else
'Step 5-C-2 Format for Images
PPSlide.Shapes.PasteSpecial DataType:=2
pp.ActiveWindow.Selection.ShapeRange.Top = 95
pp.ActiveWindow.Selection.ShapeRange.Left = 20
pp.ActiveWindow.Selection.ShapeRange.Width = 300
End If
Else
'Step 5-C-3 Format for Normal tables
PPSlide.Shapes.Paste.Select
pp.ActiveWindow.Selection.ShapeRange.Top = 95
pp.ActiveWindow.Selection.ShapeRange.Left = 20
End If
I constantly get the following error:
Selection.ShapeRange : Invalid Request. Nothing Appropriate is currently selected
Which relates to the following part of the code (where I am pasting the Excel data to the powerpoint slide and determine its position).
PPSlide.Shapes.PasteSpecial DataType:=2
pp.ActiveWindow.Selection.ShapeRange.Top = 0
pp.ActiveWindow.Selection.ShapeRange.Left = 0
pp.ActiveWindow.Selection.ShapeRange.Width = 1000
The weird thing is that this code used to work a couple of weeks ago (in excel 2016), but since today (I got downgraded to Excel 2010) it suddenly stopped working..
The full code I am using is as follows:
'Step 1: Declare variables
Dim pp As Object
Dim PPPres As Object
Dim PPSlide As Object
Dim xlwksht As Worksheet
Dim MyRange As String
Dim MyRange2 As String
Dim TemplatePath As String
'Step 2: Open PowerPoint, add a new presentation and make visible
Set pp = CreateObject("PowerPoint.Application")
Set PPPres = pp.Presentations.Add
pp.Visible = True
'Step 3: Start the loop through each worksheet
'Step 3-A: Skip Excel sheets 1 till 8
For Each xlwksht In Worksheets
If xlwksht.Index >= 9 Then
'Step 3-B: Count slides and add new blank slide as next available slide number
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
PPSlide.Select
'Step 4: Copy the Content section from Excel
MyRange = xlwksht.Range("A1").Value & ":" & xlwksht.Range("A2").Value
xlwksht.Range(MyRange).Copy
'Step 5: Paste the Content and adjust its position
'Step 5-A: Determine the Path of the Template and apply it to the Powerpoint presentation
PPPres.ApplyTemplate (TemplatePath & "\Template.potx")
'Step 5-B: Determine the PasteType
pastetype = xlwksht.Range("C1").Value 'Where C1 = "Image" for all images and tables
PasteWidth = xlwksht.Range("D1").Value 'Where D1 = "Title" then picture will fill whole screen
'Step 5-C: Based on the Pastetype paste the content in the presentation
If pastetype = "Image" Then
If PasteWidth = "Title" Then
'Step 5-C-1 Format only for Title Page
PPSlide.Shapes.PasteSpecial DataType:=2
pp.ActiveWindow.Selection.ShapeRange.Top = 0
pp.ActiveWindow.Selection.ShapeRange.Left = 0
pp.ActiveWindow.Selection.ShapeRange.Width = 1000
Else
'Step 5-C-2 Format for Images
PPSlide.Shapes.PasteSpecial DataType:=2
pp.ActiveWindow.Selection.ShapeRange.Top = 95
pp.ActiveWindow.Selection.ShapeRange.Left = 20
pp.ActiveWindow.Selection.ShapeRange.Width = 300
End If
Else
'Step 5-C-3 Format for Normal tables
PPSlide.Shapes.Paste.Select
pp.ActiveWindow.Selection.ShapeRange.Top = 95
pp.ActiveWindow.Selection.ShapeRange.Left = 20
End If