Solved

Access 2007 interacting with PowerPoint 2007

Posted on 2009-04-02
25
1,951 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now