Solved: problem specifying where to paste an object copied from Excel using the shape's name

[s.schwantes]

'Paste Excel TABLE (as a named range) as pictures in PowerPoint (Paste Special)

Sub XlChartPasteSpecial()

Dim xlApp As Object
Dim xlWrkBook As Object
Dim lCurrSlide As Long

Set xlApp = CreateObject("Excel.Application")

' Open the Excel workbook

Set xlWrkBook = xlApp.Workbooks.Open("C:\BOOK1.XLS")

' Copy named range Table1

Dim PasteRange As Boolean
Dim RangePasteType As String
Dim RangeName As String

PasteRange = True
RangeName = "Table1"
RangePasteType = "HTML"
RangeLink = True

xlWrkBook.Worksheets(1).Range("Table1").CopyPicture

' Switch back to PPT

ActiveWindow.View.GotoSlide Index:=1
ActiveWindow.Selection.SlideRange.Shapes("Rectangle 5").Select
Paste

'Paste Excel CHART as pictures in PowerPoint (Paste Special)

Set xlApp = CreateObject("Excel.Application")

' Open the Excel workbook

Set xlWrkBook = xlApp.Workbooks.Open("C:\BOOK1.XLS")

' Copy Chart

xlWrkBook.Worksheets(1).ChartObjects(1).CopyPicture

' Switch back to PPT

ActiveWindow.View.GotoSlide Index:=1
ActiveWindow.Selection.SlideRange.Shapes("Rectangle 8").Select
Paste

End Sub



:help

I've got four text boxes or placeholders on my slide. I want to specify which one to paste the table and chart from Excel into ... as is the macro above breaks on the red lines. If I omit the shape name, change Select to Paste, and delete the next line ... it works, but the objects just get dropped on the slide randomly and are not tied to any shapes. ... ?????

thanks

Steve S

[John Wilson]

After the relevant shape is selected does this work?

ActiveWindow.View.Paste

[John Wilson]

After the relevant shape is selected does this work?

ActiveWindow.View.Paste

[s.schwantes]

John,

Thanks mate!

Your suggestion did the trick. I've now got the following lines corrected and the macro works!

' Switch back to PPT
ActiveWindow.View.GotoSlide Index:=1
ActiveWindow.Selection.SlideRange.Shapes("Rectangle 5").Select
ActiveWindow.View.Paste

... and similar code below for the other object / placeholder which is Rectangle 8 for my chart.

One note of caution for others ...
After a little debugging and resetting and running again I got an error that the item w/ the specified name wasn't found. Looks like the object name changed from Rectangle 5 to Rectangle 10. I deleted that slide so that I had a new slide #1. Now all my slides have the same format and obejct names again. I'm using a slide layout w/ four text boxes, and initially all slides have the same object name (number). However, they occasionally change (mysteriously). The StarterSet tools (free) on pptools.com is very handy in identifying object names quickly.

Thanks again!

Steve Schwantes

[John Wilson]

Glad it helped and sorry about the double post!