[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Refresh PowerPoint 2010 Embedded Graphs from Access 2010 VB

Posted on 2012-08-23
5
Medium Priority
?
1,753 Views
Last Modified: 2012-08-26
I am trying to update a set of PowerPoint 2010 (FabMetrics.pptx) slides that have an embedded Excel 2010 graphs (FabMetrics.xlsx). I am keepping the source formatting & linking the data.

I am using Access 2010 (FabMetrics.accdb) VB to update the excel spreadsheet. Is it possible to use VB to refresh all of the PowerPoint slides after Access exports the new data to excel?

Also, the PowerPoint will be running as a slide show.  Do I need to close or stop the show to do the refresh?
0
Comment
Question by:Scamquist
  • 3
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
fhlio_admin earned 2000 total points
ID: 38329788
You can iterate through the slides, then shapes on slides, then charts in shapes.  You can use the refresh method in the chart to refresh the data.  You will need to have the reference to PowerPoint established in your Access DB.

Function RefreshCharts()
Dim ppApp As PowerPoint.Application, ppPres As PowerPoint.Presentation
Dim ppSlide As PowerPoint.Slide, ppShape As PowerPoint.Shape
Dim sFilePath As String

sFilePath = [Enter Your Path] & "FabMetrics.pptx"
Set ppApp = New PowerPoint.Application
ppApp.Visible = True
Set ppPres = ppApp.Presentations.Open(sFilePath)

For Each ppSlide In ppPres.Slides
    For Each ppShape In ppSlide.Shapes
        If ppShape.HasChart Then
           ppShape.Chart.Refresh
        End If
    Next ppShape
Next ppSlide

ppPres.Save
ppPres.Close
ppApp.Quit

Set ppShape = Nothing
Set ppSlide = Nothing
Set ppPres = Nothing
Set ppApp = Nothing

End Function
0
 
LVL 1

Author Comment

by:Scamquist
ID: 38330161
When I run the function, I get the error


Run-time error '-2147467259 (80004005)':

Presentation (unknown member): The presentation is read-only and must be saved with a different name.

The hang comes at
ppPres.Save


I have a running display on in my factory.  Does the slideshow need to stop during the refresh?
0
 
LVL 6

Expert Comment

by:fhlio_admin
ID: 38330235
You can not refresh the PowerPoint from Access and save the results when the presentation is open.  It is erroring because it is opening a second version and because the first is already open, it defaults to read-only.

The only other solution is to write the code within PowerPoint and push that code to refresh within the application.
0
 
LVL 6

Expert Comment

by:fhlio_admin
ID: 38330245
Or, if you need this as a contstant item, it could be ran continuously through the access application and run directly on your displaying machine.
0
 
LVL 1

Author Closing Comment

by:Scamquist
ID: 38334027
Thank you for your help.  I think I will have access stop PowerPoint, Refresh and open it up again.  Your code will help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

834 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