Office 2003 macro won't run in Office 2007

[Nickis]

I created a macro in Office 2003 using early binding to copy data from Excel
into Powerpoint...not the greatest code...but it works! Now my users are
switching to Office 2007 and the code won't run! I have include the relevant
string of code below.

Dim objPPT As Object
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

Set PPApp = GetObject(, "Powerpoint.Application")
PPApp.ActivePresentation.Slides.range(Array(13)).Select

' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
' Reference active slide
Set PPSlide =
PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

'EXCEL CODE
Sheets("TablesforPP").Select
range("a2").Select
Selection.Copy

'POWERPOINT CODE
PPSlide.Shapes.Paste.Select

This is where it errors with the following message:

"Run-time error '-2147188160(80048240)':
Shapes (unknown member): Invalid request. Clipboard is empty or contains
data which may not be pasted here"

I don't need this coding to be compatible with both Office 2003 and 2007, I
just need it to work in Office 2007. I'm sure I will run into more issues as
I cycle through the code, but if I can understand why this is happening and
fix it, I think I'll be a long way to understanding any subsequent issues.

Thanks,
Nicki

 

[joel]

Check if the cells in the workbook are selected to see if the problem si
in excel or powerpoint. I wouldmake the following change

from
'EXCEL CODE
Sheets("TablesforPP").Select
range("a2").Select
Selection.Copy


to
with Thisworkbook
'EXCEL CODE
Sheets("TablesforPP").range("a2").Copy
end with

 

[Nickis]

Check if the cells in the workbook are selected to see if the problem si
in excel or powerpoint. I wouldmake the following change

from
'EXCEL CODE
Sheets("TablesforPP").Select
range("a2").Select
Selection.Copy


to
with Thisworkbook
'EXCEL CODE
.Sheets("TablesforPP").range("a2").Copy
end with


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=170126

Microsoft Office Help

.
Sorry....yes I should have stated that the cell in Excel IS selected. The problem is in the paste to Powerpoint.

Nicki

 

[joel]

If you manually went into the clip board will the object paste int
Power Point? Can you manually copy the object from Excel into Powe
Point.

I'm thinking you may need to paste using Pastespecial instead of Paste
Try manualy copying ans and pasting using Pastespecial and find ou
which Pastespecial options gives you the results you are looking for

 

[Nickis]

No...that's not it.

I can manually paste...and can paste special. I'm thinking it's something
to do with the code and how Powerpoint is treating the "shape". Not sure
what the code is to "pastespecial" in powerpoint. My "shape" looks more like
I would expect when I paste special in "Formatted Text (RTF)" but I'm not
sure of the Powerpoint code to perform this, and the macro recorder in 2003
doesn't help (there is no macro recorder in Powerpoint 2007!).

Maybe if I search/post over on the Powerpoint site I might get some insight?

Nicki

 

[joel]

There are a few ways of getting the VBA commands for Power Point

1) Look at the help at Power Point
2) The method are different in power point than excel. To get th
Power Point Library you should add the reference into Excel VBA by usin
VBA menu menu : Tools - Reference - Microsoft Power Point xx.x objec
Library
3) You can record a macro in Power Point and copy text from one slid
in Power Point to another slide. the adapt the Power Point code int
your excel macro.
4) You don't necessarily need to add the reference library to excel a
long as the Property values are call out by numbers instead of th
Constants defined in the library. You can open the Object Browser i
both excel and Point Point VBA. It is under View or just right clic
the module sheet in VBA.

At the Top of the Object browser is a binocular Icon and a box to th
left. If you put into the box to the right of the Icon (in excel) an
type xlpastevalue you will se the value -4163 and all the other option
for excel PasteSpecial. No if you add the reference to excel for powe
point you will also find ppastedatatype for the power point propert
values.

If you don't have the power point reference specified excel will us
excel pastespecial default value into power point and you may not ge
the correct results. Power point except value 1 to 11. So th
exceptable values in excel doesn't match the acceptable values in powe
point. I haven't checked if this is true with a regular paste