Solved: I can't figure out how to copy paste a named range in Excel to ppt

[s.schwantes]

:banghead:

I have the following which works for a chart, but I also want to copy in the data that created the chart as a separate table. I've named the range of cells as "Table1". I've tried editing the red line below to this:


Worksheets(1).Range("Table1").Copy

but that doesn't work. :dunno




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

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

' Switch back to PPT

ActiveWindow.View.GotoSlide Index:=1
ActiveWindow.Selection.SlideRange.Shapes.Paste

End Sub
Any ideas ... any one?

Thanks!

I've a follow up question also. The working macro above just dumps the pasted object arbitrarily onto the slide. My slide layout is basically the 4 up format, i.e., I have a title bar and then four boxes for content. I'd like to be able to specify the box into which the chart or table should be pasted. By using the recorder and clicking on (selecting) the boxes, I was able to identify the proper approach, e.g.

ActiveWindow.Selection.SlideRange.Shapes("Rectangle 8").Select

I tried to use this snippet and change "Select" to "Paste" but no luck there. I keep running into method or other errors.

Thanks again,

Steve S.

[s.schwantes]

I've solved part one posted here. I've got a new post addressing part 2, i.e., how to specify where to paste an object on a slide.

Thanks,

Steve S.

[TrippyTom]

Hi Steve,
I'm betting the reason it's not working is because the object you're trying to select isn't always going to be named "Rectangle 8". I think a better way to handle placing your chart is to figure out the TOP and LEFT attributes.

For instance, to paste your selection (be it chart or whatever) in the upper right quadrant, I set the slide to be "Title and 4 content". Then I double click the upper right quadrant to look at its position. Then I multiply those values by 72 (screen resolution) to get the values we need for our code:
Horizontal (left) = 5.08" x 72 ppi = 365.76
Vertical (top) = 1.7" x 72 ppi = 122.4

This code will place whatever's selected in the upper right quadrant:

Sub PlaceTest()
Dim myslide As Long
myslide = ActiveWindow.View.Slide.SlideIndex
With ActiveWindow.Selection.ShapeRange
.Left = 365.76
.Top = 122.4
End With
End Sub


You would do the same for the other 3 quadrants.