newby question about VBA to coy Excel Object

  • Thread starter Thread starter Glen Mettler
  • Start date Start date
G

Glen Mettler

Am using PPT 2002 and Excel 2002 and xp
I have a ppt file that has several slides that contain excel spreadsheets
pasted as picture
I want to automate this with vba.

in ppt
1 open predefined Excel file
2 select sheet (always the same one)
3 run excel macro to populate sheet
3 select area to copy (always the same columns, rows vary)
4 go back to ppt and delete current picture
5 paste special, picture
6 size picture to fit selected region on slide

Is it possible to automate that with vba?

Thanks
Glen
 
Shyam,
Thanks for the reply. The code snippet refers to selecting and copying a
chart. I need to select and copy selected rows and columns. I have tried
to modify it to select and copy sheet rows and columns (named range is
"StatusTab") but I can't seem to get it to work. I dont' get an error but
the only thing I get in ppt is "Worksheet("Status)" in a text box. Any
suggestions
================
Sub XlChartPasteSpecial()

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

myname = Application.ActivePresentation.Name
mypath = Application.ActivePresentation.Path
myexceldate = Right(myname, 12)
myexceldate = "\CART " & Left(myexceldate, 8) & ".XLS"

myexcel = mypath & myexceldate

Set xlApp = CreateObject("Excel.Application")

' Open the Excel workbook

Set xlWrkBook = xlApp.Workbooks.Open(myexcel)

' Copy picture of the 1st chart object onto the clipboard

'xlWrkBook.Worksheets(1).ChartObjects(1).copypicture
xlWrkBook.Worksheets("Status").Select
xlWrkBook.Worksheets("Status").Range("StatusTab").Select
xlWrkBook.Worksheets("Status").Copy

' Get the slide number

lCurrSlide = ActiveWindow.Selection.SlideRange.SlideNumber

' Paste the picture onto the PowerPoint slide.

'ActivePresentation.Slides(lCurrSlide).Shapes.Paste
ActivePresentation.Slides(lCurrSlide).Shapes.Paste
' Close the open workbook without saving changes

xlWrkBook.Close (False)
xlApp.Quit

Set xlApp = Nothing
Set xlWrkBook = Nothing

End Sub
 
xlWrkBook.Worksheets("Status").Range("StatusTab").Select
xlWrkBook.Worksheets("Status").Copy
Click to expand...


You need to change this code to copy the range which you are not
doing.

xlWrkbook.Sheets("Status").Range("StatusTab").Copy

You also don't need to actually select it to copy it.

Brian Reilly, PowerPoint MVP
 
Back
Top