Solved

Edit underlying spreadsheet data (linked to another Excel file) that sits behind PPT 2007 Chart

Posted on 2010-11-22
18
1,193 Views
Last Modified: 2012-06-27
Having had some experience in automating ppt charts I realise that this doesn't look good.

From http://www.pptfaq.com/FAQ00889.htm, "If you need to automate charts in PowerPoint 2007, it'd be best if you change your name and move to another town where nobody knows that you do PowerPoint programming.

The chart objects you get in PowerPoint 2007 when you add a new chart expose no methods or properties to VBA. In short, you can't do anything with them in code."

With that beginning in mind
1) I have a series of PPT charts with data from the 'Edit Data' option in underling Excel spreadsheets
2) The data series are in turn linked to another spreadsheet (see red box in screenshot)

Is it possible to change the linked file source manually or programmatically?

Pls note that this issue does not relate to edit OLE Links, the "Prepare" option under the Office Button does not show any links as these are native ppt charts.

Lastly pls do not reply to this question unless you can offer specific feedback to the problem.  I have googled this at some length already :)

Regards

Dave






ppt2007.png
0
Comment
Question by:Dave Brett
  • 9
  • 8
18 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34194419
Manually is pretty easy. Just click 'Edit Data' and do a find/replace and replace the old path with the new one.
I'll check into the code solution.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34194556
There is a sendkeys method. So if you can manage to select the chart and send Alt+J Alt+C Alt+D it will open up the datasheet and you can capture it as an Excel object and do whatever you want.
Something like this, but I can't seem to get it to activate the right window before sending the keys.
I need to go sleep, so I'll leave you with this and come back to it after work tomorrow.
Dim s As Slide
Dim c As Shape

ActivePresentation.Windows(1).Activate

For Each s In ActivePresentation.Slides
  For Each c In s.Shapes
  s.Select
    If c.Type = msoChart Then
      c.Select
      SendKeys "%J%C%D"
    End If
  Next
Next

Open in new window

0
 
LVL 50

Author Comment

by:Dave Brett
ID: 34214754
> Manually is pretty easy. Just click 'Edit Data' and do a find/replace and replace the old path with the new one.

Tommy

Thx but I am talking 100 charts here. So by manual I meant is there a menu option I'd missed - sorry, I wasn't clerar here

> There is a sendkeys method.

That sounds promising, I do tend to leave Sendkeys in the locker but it may need to be the last resort. I will tinker with that, it might be fiddly though to grab the underlying Excel object that relates to PPT

Cheers

Dave
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 500 total points
ID: 34215156
Yes. It won't be easy. I'm curious, so I'll probably play with it some tomorrow too. I wish there was an easy way, but Powerpoint kind of got neglected when they put in all the VBA stuff.
0
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 0 total points
ID: 34235886
Tommy,

A progress update

This is abouut 80% there, albeit kludgy.

I couldn't get
SendKeys "%J%C%D"
to work even though it does work manually, however this did get me to the chart data
SendKeys "+{F10}+E", 2

The 20% remaining
- an upfront fix to check for open Excel instances at code start and if so close code. Window dressing for the end
- of more concern is that although this code updates all the links in Xl, the ppt charts are not updated by the code instantly. I need to click Edit Data for the charts to "see" the new links. I have tried DoEvents, activating the PPT window and other sendkey options to no avail
- the file corrupts on every 1 run in 10 or so

Cheers

Dave


Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub GetEm()
    Dim s As Slide
    Dim c As Shape
    Dim objExcel As Excel.Application
    Dim objWB As Workbook


    ActivePresentation.Windows(1).Activate
    For Each s In ActivePresentation.Slides
        s.Select
        For Each c In s.Shapes
            If c.Type = msoChart Then
                Debug.Print s.SlideNumber & c.Name
                c.Select
                SendKeys "+{F10}+E", 2
                Do While objExcel Is Nothing
                    Set objExcel = GetObject(, "Excel.application")
                Loop
                Set objWB = objExcel.ActiveWorkbook
                On Error Resume Next
                objWB.ChangeLink Name:= _
                                 "Z:\04\A\Reborn summary.xlsm", NewName:= _
                                 "Z:\04\B\Reborn summary.xlsm", Type:=xlExcelLinks
                On Error GoTo 0
                Sleep 1000
                objWB.Close False
                objExcel.Quit
                Set objExcel = Nothing
                End If
        Next
    Next
End Sub

Open in new window

0
 
LVL 50

Author Comment

by:Dave Brett
ID: 34263299
Tommy,

Did you play any further with this?

I think this has gone as far as it can be taken

Cheers

Dave
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34267337
You can capture Excel instances by the window name, so I wouldn't think you'd need to write code to close other instances.
0
 
LVL 50

Author Comment

by:Dave Brett
ID: 34271621
This is the bit irritating me.

"of more concern is that although this code updates all the links in Xl, the ppt charts are not updated by the code instantly. I need to click Edit Data for the charts to "see" the new links. I have tried DoEvents, activating the PPT window and other sendkey options to no avail"
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34274751
Are the links not stored in the cells? From the image you posted, it looked like the cells had the links in them.
Are you just missing the objExcel.ActiveWorkbook.ActiveSheet.Calculate command?

This code worked for me.
Private Sub GotEm()
    Dim s As Slide
    Dim c As Shape
    Dim objExcel As Excel.Application
    Dim objWB As Workbook
    Dim r As Excel.Range


    ActivePresentation.Windows(1).Activate
    For Each s In ActivePresentation.Slides
        s.Select
        For Each c In s.Shapes
            If c.Type = msoChart Then
                Debug.Print s.SlideNumber & c.Name
                c.Select
                SendKeys "+{F10}+E", 2
                Do While objExcel Is Nothing
                    Set objExcel = GetObject(, "Excel.application")
                Loop
                Set objWB = objExcel.ActiveWorkbook
                For Each r In objExcel.ActiveWorkbook.ActiveSheet.UsedRange
                  r.Formula = Replace(r.Formula, "data.xls", "data2.xls")
                Next
                objExcel.ActiveWorkbook.ActiveSheet.Calculate
                objExcel.Wait (TimeSerial(0, 0, 1))
                objWB.Close False
                objExcel.Quit
                Set objExcel = Nothing
                End If
        Next
    Next
End Sub

Open in new window

0
 
LVL 50

Author Comment

by:Dave Brett
ID: 34275885
> Are the links not stored in the cells? From the image you posted, it looked like the cells had the links in them

They are

Excel updates fine with my code  (will test your tomorrow). But the ppt charts do not automatically "see" the new data until I open the underlying spreadhseets manually

Cheers

Dave
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34277493
The ppt charts updated with mine with the test data I gave it. Hope it works for you.
0
 
LVL 50

Author Closing Comment

by:Dave Brett
ID: 34317292
I couldn't force the updates with the last code

And I found the sleep, plus edit links, more reliable than the formula replace

Points awarded for the sendkeys suggestion

Thx Dave
0
 
LVL 50

Author Comment

by:Dave Brett
ID: 36287443
Actually - ""If you need to automate charts in PowerPoint 2007, it'd be best if you change your name and move to another town where nobody knows that you do PowerPoint programming" - is incorrect.

It is possible to access then manipulate the underlying workbook beneath the chart data

If the first shape on slide 1 is a chart then the code below writes the value 10 to cell A1

Regards

Dave
Dim ppShp As Shape
    Dim chr As Chart
    Set ppShp = ActivePresentation.Slides(1).Shapes(1)
    If ppShp.HasChart Then
        Set chr = ppShp.Chart
        Set xls = chr.ChartData.Workbook
    End If
    xls.sheets(1).[a1] = 10

Open in new window

0
 

Expert Comment

by:duup
ID: 36446681
@brettdj: thanks but i think that will not work as the sheet.activate will pop up the excel file in slideshow mode and that is what i want to avoid...
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36449090
This should probably be opened as a new question, but you shouldn't be using sheet.activate to modify an Excel sheet. sheet.activate is a terrible practice that is spread because that's what 'record macro' uses. You should be able to do anything you want to the Excel sheet without ever activating it.
0
 
LVL 50

Author Comment

by:Dave Brett
ID: 37360527
Tommy,

The activate mentioned refers to the PPT ChartData (which appears to have disappeared from my code above??) and this is fundamental to exposing Excel to PPT.

Dave


Sub ChangeChartData()
   
   Dim myChart As Chart
   Dim myChartData As ChartData
   Dim myWorkBook As Excel.Workbook
   Dim myWorkSheet As Excel.Worksheet

   Set myChart = ActivePresentation.Slides(1).Shapes(1).Chart
   Set myChartData = myChart.ChartData
   
   myChartData.Activate
   
   Set myWorkBook = myChartData.Workbook
   Set myWorkSheet = myWorkBook.Worksheets(1)
   
   'Code to edit links and recalc goes here instead
   myWorkSheet.Range("C3").Value = -100
   
   myWorkBook.Close True
   
   Set myChart = Nothing
   Set myWorkBook = Nothing
   Set myChartData = Nothing
   Set myChart = Nothing
   
End Sub

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 37363974
Is that a question or a clarification?
0
 
LVL 50

Author Comment

by:Dave Brett
ID: 37365185
It was a reponse to your comment here

"This should probably be opened as a new question, but you shouldn't be using sheet.activate to modify an Excel sheet. sheet.activate is a terrible practice that is spread because that's what 'record macro' uses. You should be able to do anything you want to the Excel sheet without ever activating it. "

as I wasn't clear on where you were directing the sheet.activate comment at - i think it was from duup's question which I had posted into and linked back here. duup's question is now deleted
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel formula to show $0.00 if negative number. 7 52
Excel 2007 VB Code for GCF 7 13
V-Lookup 11 17
VBA working with shapes 6 9
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

808 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