PDA

View Full Version : VBA Powerpoint formating



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

snb
12-14-2017, 03:57 AM
Please use code tags !

Without seeing a file it's hard to analyse the problem.

ELSUIS
12-14-2017, 06:57 AM
Attached the document where I get my error..

But to be honest, I dont think that there is something wrong with my code.
Because it used to work in Excel 2016, but since my downgrade to Excel 2010 this error pops up.
And it doesn't only occur in this file, but all files where I use this line of code the error comes up (although all of them used to work in the past..).

snb
12-14-2017, 09:05 AM
Sub M_snb()
With CreateObject("PowerPoint.Application")
With .Presentations.Add
For Each it In Worksheets
it.Range("A1:A2").Copy
With .Slides.Add(1, 12)
.Shapes.PasteSpecial 2
With .Shapes(1)
.Top = 80
.Left = 7.2
.Width = 600
End With
End With
Next
End With
End With
End Sub

p45cal
12-14-2017, 10:33 AM
This worked in Excel/PowerPoint 2010:
Sub PrintPPT1()
Dim pp As Object, PPPres As Object, PPSlide As Object, xlwksht As Worksheet
Dim MyRange As String, SlideCount As Long

Set pp = CreateObject("PowerPoint.Application")
Set PPPres = pp.Presentations.Add
pp.Visible = True
For Each xlwksht In ActiveWorkbook.Worksheets
MyRange = xlwksht.Range("A1").Value & ":" & xlwksht.Range("A2").Value
xlwksht.Range(MyRange).Copy
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
' PPPres.ApplyTemplate ("C:\Users\maasro\Documents\Custom Office Templates\AR2 REVIEW PRESENTATION NEW!!!.potx")
With PPSlide.Shapes.PasteSpecial(DataType:=2)
.Top = 80
.Left = 7.2
.Width = 600
End With
Next xlwksht
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing
End Sub

mancubus
12-15-2017, 01:18 AM
since the first 8 sheets will be skipped a condition is needed to test this in the loop:



For Each ws In Worksheets
If ws.Index > 8 Then
'rest of the statements in the loop
End If
Next ws

ELSUIS
12-15-2017, 01:40 AM
This worked in Excel/PowerPoint 2010:
Sub PrintPPT1()
Dim pp As Object, PPPres As Object, PPSlide As Object, xlwksht As Worksheet
Dim MyRange As String, SlideCount As Long

Set pp = CreateObject("PowerPoint.Application")
Set PPPres = pp.Presentations.Add
pp.Visible = True
For Each xlwksht In ActiveWorkbook.Worksheets
MyRange = xlwksht.Range("A1").Value & ":" & xlwksht.Range("A2").Value
xlwksht.Range(MyRange).Copy
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
' PPPres.ApplyTemplate ("C:\Users\Template.potx")
With PPSlide.Shapes.PasteSpecial(DataType:=2)
.Top = 80
.Left = 7.2
.Width = 600
End With
Next xlwksht
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing
End Sub


Great! Thanks for your help p45cal! This fixed the problem indeed for the pictures!
However I have one additional question as well, because I also used the following code to paste and excel range without formatting changes to Powerpoint.
This one still gets an error and I can't find the Excel 2010 replacement for the first line of code:


PPSlide.Shapes.Paste.Select
pp.ActiveWindow.Selection.ShapeRange.Top = 10
pp.ActiveWindow.Selection.ShapeRange.Left = 20


New code:

With PPSlide.Shapes.Paste.Select
.Top = 10
.Left = 20
End With


Does anybody know with what I should replace the first line of code to get it working again in excel 2010??

snb
12-15-2017, 01:54 AM
Did you overlook #4 ?

ELSUIS
12-15-2017, 02:48 AM
Did you overlook #4 ?

Hi snb, I certainly didn't overlook it, thanks for providing an alternative solution! However this would imply that my entire code would be changed.. :-) As the code I posted is only a segment of the original code to demonstrate where I get an error, I was only looking to resolve the issue that occurred since my downgrade to Excel 2010.

But do you perhaps know how to resolve the issue I am still having with the Paste.Select?


PPSlide.Shapes.Paste.Select

p45cal
12-15-2017, 04:11 AM
I can't test right now, but try just missing off the .Select so it becomes:
With PPSlide.Shapes.Paste

snb
12-15-2017, 04:30 AM
It would only mean that your whole code would not be changed, but improved instead.
Never use select as you can see in the code I posted.

ELSUIS
12-15-2017, 05:07 AM
I can't test right now, but try just missing off the .Select so it becomes:
With PPSlide.Shapes.Paste

If I remove the select, I get the message that:
"Invalid Request. Clipboard is empty or contains data which may not be pasted here"

However if I go to the powerpoint and press CTRL+V, the exact table that I want to have pasted is pasted in the powerpoint..
So that error message doesn't really make sense, as there is something in my clipboard and it can be pasted in the powerpoint presentation..

Anyone a clue?

ELSUIS
12-15-2017, 05:47 AM
It would only mean that your whole code would not be changed, but improved instead.
Never use select as you can see in the code I posted.

It definitely looks way slimmer and directer.. However I dont think I have the knowledge/skills to fully adjust the full code to meet the level in your script :-)

p45cal
12-15-2017, 11:28 AM
If I remove the select, I get the message that:
"Invalid Request. Clipboard is empty or contains data which may not be pasted here"

However if I go to the powerpoint and press CTRL+V, the exact table that I want to have pasted is pasted in the powerpoint..
So that error message doesn't really make sense, as there is something in my clipboard and it can be pasted in the powerpoint presentation.. Well this opened a can of worms!
It turns out, for Office 2010, that there's no PasteSpecial argument that works for pasting Excel ranges (It may work from VBA in PowerPoint, but I haven't tried).
Yes, you can do it manually with Ctrl+c and Ctrl+v, and you get a table in PowerPoint.
Many people have asked about doing this in VBA. So after more fishing on the interweb the best I found for Office 2010 is something along the lines of:
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)
PPSlide.Select 'this is needed!
pp.CommandBars.ExecuteMso ("PasteExcelTableSourceFormatting") 'yuk
For i = 1 To 300: DoEvents: Next 'you may have to increase the number of loops.
With PPSlide.Shapes(PPSlide.Shapes.Count)'not a very robust method of getting at what's just been pasted.
.Top = 80
.Left = 7.2
.Width = 600
End With

Apart from that, I'm clueless.

snb
12-17-2017, 03:57 AM
To create a slide with a linked Excel table; i.c. G:\OF\powerpoint.xlsb; sheets("Sheet1").range("C6:F17"):


Sub M_snb_Excelrange_gekoppeld()
With CreateObject("powerpoint.application")
.Visible = True
With .presentations.Add
With .slides.Add(1, 12)
.Shapes.AddOLEObject 20, 20, , , , "G:\OF\powerpoint.xlsb!Sheet1!R6C3:R17C6", , , , , True ' = Sheet1.Range("C6:F17")
End With
End With
End With
End Sub

It's better inserting objects than copy-pasting.