Pastespecial sub broken

[werafa]

Hi all,

This sub used to work fine, and has suddenly decided to break.
It takes an excel range, pastes as text, and sets some formatting.
it is now breaking at the "Set MyShapeRange....."

Sub PasteFooter()
'paste text from excel as unformatted text,
'remove extra blanks inserted by excel 2013 when using centred allignment
'then resize to full width
Dim myShapeRange As ShapeRange
Dim myShape As Shape
Dim myString As String
Set myShapeRange = ActiveWindow.Selection.SlideRange(1).Shapes.PasteSpecial(ppPasteText)
Set myShape = myShapeRange(myShapeRange.Count)

With myShape
myString = .TextFrame.TextRange.Text
If myString = "" Then myString = "Add Text"
Do While InStr(myString, " ") > 0
myString = Replace(myString, " ", " ")
Loop
Do While InStr(myString, vbTab) > 0
myString = Replace(myString, vbTab, "")
Loop
.TextFrame.TextRange.Text = myString
.TextFrame.TextRange.ParagraphFormat.Bullet = msoTrue
.TextFrame.TextRange.ParagraphFormat.Bullet.Type = ppBulletUnnumbered
.LockAspectRatio = False
.Top = 480 'points from bottom
.Left = 20 'points from left
.Width = 680 'points wide
.Height = 60 'points high
End With
Set myShape = Nothing
Set myShapeRange = Nothing
End Sub
The error is: Shapes (unknown Member): Invalid request. the specified data type is unavailable.

Does anyone know what would cause this? Windows has just installed some updates - could it be a bad codeing habit that is now blocked?

Thanks
Tim

[John Wilson]

Were you using in 2013 when it worked?

Seems to me it's broken if you copy from Excel 2013 but OK in 2010?

[John Wilson]

You code SHOULD work but it doesn't (Microsoft wake up)

See if this variation works in both 2010 and 2013

Sub PasteFooter()
'paste text from excel as unformatted text,
'remove extra blanks inserted by excel 2013 when using centred allignment
'then resize to full width
Dim osld As Slide
Dim myShape As Shape
Dim myString As String
Set osld = ActiveWindow.Selection.SlideRange(1)
Set myShape = osld.Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 10, 200, 20)
myShape.Select
ActiveWindow.View.PasteSpecial (ppPasteText)
With myShape
myString = .TextFrame.TextRange.Text
myString = Trim(myString) ' maybe this, not sure what you are doing here
Do While InStr(myString, " ") > 0
myString = Replace(myString, " ", " ")
Loop
Do While InStr(myString, vbTab) > 0
myString = Replace(myString, vbTab, "")
Loop
.TextFrame.TextRange.Text = myString
.TextFrame.TextRange.ParagraphFormat.Bullet = msoTrue
.TextFrame.TextRange.ParagraphFormat.Bullet.Type = ppBulletUnnumbered
.LockAspectRatio = False
.Top = 480 'points from top
.Left = 20 'points from left
.Width = 680 ' points width
.Height = 60 'points high
End With
Set myShape = Nothing
Set myShapeRange = Nothing
End Sub

[werafa]

Thought I'd try after a reboot, but it is still broken. It used to work fine in 2013, then one day microsoft improved things.....

Your edits do work, so thank you very much.
( and the do whiles are to strip out the unwanted formatting that microsoft so kindly adds in excel 2013)

If I understand this correctly, the old method of pasting text no-longer calls the 'create text box' bit (it fails at shapes.pastespecial). Hence there is now a need to create the text box before populating it.
Tim