Solved

Access 2007 interacting with PowerPoint 2007

Posted on 2009-04-02
25
1,954 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
PowerPoint is the go-to presentation software for millions of users around the world. Many presentations use basic text features but you can really make special text jump out of your slide by applying this bubble text design process. This article ha…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

786 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