Solved

Access 2007 interacting with PowerPoint 2007

Posted on 2009-04-02
25
1,963 Views
Last Modified: 2013-11-27
I have an Access 2003 App that I have converted to Access 2007 -- a dictated migration I am stuck with.

The app interacts programmatically with PowerPoint.  The attached code worked fine with Access 2003 and PowerPoint 2003.  But, it fails in 2007.
The instruction:
pptSlide.Shapes.Paste
gets error:
Method 'Paste' of object 'Shapes' failed

I tried to go into PowerPoint 2007 to record a macro to try to understand how to do this properly, but of course, Microsoft has eliminated the macro recorder!

What can I do to get my code to run?
g_Four_R_GraphObj.ChartArea.Copy
        With pptApp
            With pptPresentation
                If .Slides.count = 0 Then
                    Set pptSlide = .Slides.Add(1, ppLayoutTitleOnly)
                Else
                    lngCurrentSlideIndex = pptApp.ActiveWindow.View.Slide.SlideIndex + 1
                    Set pptSlide = .Slides.Add(lngCurrentSlideIndex, ppLayoutTitleOnly)
                End If
                
                sText = Me.Chart_Title
                pptSlide.Shapes.Title.TextFrame _
                                .TextRange.Text = sText
                pptSlide.Shapes.Title.TextFrame.TextRange.Font.Size = 28
                pptSlide.Shapes.Title.TextFrame.TextRange.Font.Bold = msoTrue
                
                pptSlide.Shapes.Paste
                With pptSlide.Shapes(pptSlide.Shapes.count)
                    .Left = 42
                    .Top = 114
                    .Width = 637
                    .Height = 377
                End With
                
                sText = Parent.Quadrant_Counts
                pptSlide.Shapes.AddTextbox(msoTextOrientationHorizontal, 42, 486, 637, 15).TextFrame _
                            .TextRange.Text = sText
                pptSlide.Shapes(pptSlide.Shapes.count).TextFrame _
                                    .TextRange.Font.Size = 12
                pptSlide.Shapes(pptSlide.Shapes.count).TextFrame _
                                    .TextRange.Font.Bold = msoTrue
                pptSlide.Shapes(pptSlide.Shapes.count).TextFrame _
                                    .TextRange.ParagraphFormat.Alignment = ppAlignCenter
 
            End With
        End With

Open in new window

0
Comment
Question by:wsturdev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 2
  • +2
25 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24052035
I can't help you with this. But just googling about the issue, it sounds like PPT2007 is an absolute mess.  Puts the problems with Access 2007 in the shade.  You have my sympathy.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24066691
wsturdev,

The syntax is unchanged in 2007.  From the scope of the snippet it is not obvious that the paste buffer is populated, are you certain the buffer has a shape at that point?

Chris
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24076696
I am sure the graph is in the clipboard.  When I stop the code immediately after...
g_Four_R_GraphObj.ChartArea.Copy
...and then open a PowerPoint document, a Word Document or an Excel document from the desktop and select Paste, the chart gets pasted into the document.
 
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24077608
Try adding doevents to see if it's a timing issue?

                pptSlide.Shapes.Paste
                DoEvents
                With pptSlide.Shapes(pptSlide.Shapes.count)

Chris
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24078677
Chris,
Putt a DoEvents after the command that gets the error?  The error will already have occurred.
I put one immediately after the copy and just before the paste, and neither one changed the outcome.  I still get the error.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24079088
Quite right so it doesn't seem to be timing then

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24079129
Clutching at straws then, does it fail the same if there is a pre-existing slide and no slides at all?

Chris
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24080796
Chris,
When I try to origrammatically copy and paste the chart into a PPTX that is already open, with 1 slide in it, I get a different error:
View (unknown member): Invalid Request.  No slide is currently in view.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24081120
SO to try and gather the evidence so someone clever can make sens:

With a slide in the PPT:
pptSlide.Shapes.Paste
gets error:
View (unknown member): Invalid Request.  No slide is currently in view

Whereas with no (initial) slide in the presentation you get
pptSlide.Shapes.Paste
gets error:
Method 'Paste' of object 'Shapes' failed

Chris
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24081594
Yes
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24084208
I am reminded that PPT automation from elsewhere has a fundamental issue with visibility therefore try:

        With pptApp
            .visible = true
            ........

Chris
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24086647
The same error occurs.
Remember, also, that in my initial description, this all worked with Office 2003.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24086782
Yep understood but whilst i'm not certain that visibility is an issue with earlier versions hence the check.  But i'm stumped again so will have to think further

Chris
0
 
LVL 21

Expert Comment

by:GlennaShaw
ID: 24088698
This may be because PowerPoint 2007 doesn't use MSGraph as the charting engine.  It now uses Excel.  See: http://msdn.microsoft.com/en-us/library/bb871574(VS.80).aspx#PP07_VB_topic5
The PowerPoint 2007 Object Model Reference is available here: http://msdn.microsoft.com/en-us/library/bb265987.aspx

I'm just not that skilled with VB to debug your code for you, but folks here are:
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.powerpoint&lang=en&cr=US
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24093107
Okay -- that would solve the problem if I was trying to build a chart within PPT2007.  I will check the links.
But, what I am trying to do is to paste a copied chart.  I can stop the code immediately after the copy, then go to PPT and manually paste the chart.  If I can do it manually, then why can I not paste it programmatically?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 24095454
not a powerpoint user, but have done a fair bit of vba.

i went in to ppoint 2007 view, macro's, typed a name and hit create...

when i started to replicate your code, i found that:

Set pptslide = pptPresentation.Slides.Add does not exist

needs to be

Set pptslide = pptPresentation.Slides.AddSlide(1, ppLayoutTitleOnly)

but this gives an error as i have not defined ppLayoutTitleOnly as a custom layout

you need to do the same i think... go into powerpoint and work through your code finding all the lovely fun changes to VBA in powerpoint in order to get your code orking there... then port it back into your access mdb inside your vba With statements...

one bit at a time.

e.g. i poked around a bit, manually copied an excel chart and ran the code below and it worked....

so it could be that there is some problem with access chars to powerpoint?  
Sub dcxcc()
 
ActivePresentation.Slides(1).Shapes.Paste
 
End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24095588
Sudonim

For your information the method slides.add carries over from earlier version whereas I think addslides is new to 2007.

Therefore it works fine in 2007 and I cannot begin to say why it doesn't work for you.

Chris
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24095606
But "Set pptslide = pptPresentation.Slides.Add" does work, as I am able to use the code to add a slide and then create PPT tables dynamically.
I have been looking at the PasteSpecial method, and have tried every datatype available to try to understand.
So far, I think the reason I cannot paste the chart is because the datatype in the clipboard is unrecognizable by PPT.  Maybe I can find a way to copy the chart as something specific that would meet PPT's requirements.
But, I keep going back to the fact I can do the copy, interrupt the code, then go to the PPT object I am trying to get the chart into and simply paste it and it works!
As GlennaShaw suggested, I went to the Microsoft newsgroups and posted a question, but in the Access Programming forum, not in the PPT forum.  I will now also post an identical question in the PPT forum there.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24095720
I almost hate to add this because I do not think I want to go down this path yet...
I am using MS Graph to create the graphs in Access -- and I have many of them, and lots of logic is involved.  I know I am supposed to be able to create the graphs in Access using an Excel graphing object, but I remember a long time ago choosing Graph over Excel (and this memory is admittedly vague now) because it was easier to do what I needed to do, and with more control.
As a last resort, I could change my logic to do the graphs in Excel objects inside Access which, throretically I could then copy into PPT, but I have a production user tapping his toe, waiting for a solution to this problem NOW, and I have no confidence I can successfully convert my code quickly and still have it work exactly as needed the first time.
The other option is, create the charts in Graph inside Access, then rebuild the charts exactly the same within PPT.  But, again, that will require a bunch of code changes in the PPT generation routines.
The bottom line on this train of thought is I would rather find out how to make the current copy/paste work and reserve the concepts discussed here for when I have more leisure time.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24095782
I am not sure what this tells me, but after copying the graph in Access and then manually pasting it into PPT, I double clicked on it hoping to discover something about it, and got a message:
"To edit this chart using the new features available in the 2007 Microsoft Office system, you must first convert it to the 2007 Office system format.  Do you want to convert this chart to the new format?"
So PPT recognizes it as a chart!!??!!
0
 
LVL 1

Author Comment

by:wsturdev
ID: 24095804
Sorry - hit Submit too quickly...
The options on that dialog are:  Convert, Convert All, or Edit Existing.  When I click Edit Existing, it lets me work with the chart in MS Graph mode, the exact same way I could while it was in Access!!
0
 
LVL 21

Accepted Solution

by:
GlennaShaw earned 500 total points
ID: 24096135
Yeah, basically it's treating it as if it's a PowerPoint 2003 file and PowerPoint 2007 allows for the the backward compatibility but I recommend converting it to an Excel graph if you're going to use this going forward.
Check the code here for the Copy/Paste method: http://peltiertech.com/Excel/XL_PPT.html
Although it's Excel to Word/PowerPoint you should be able to adapt it for Access.  You'll need to scroll down.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question