asked on

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

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

TommySzalapski

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.

ASKER CERTIFIED SOLUTION

TommySzalapski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave

ASKER

> 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

SOLUTION

TommySzalapski

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave

ASKER

Tommy,

Did you play any further with this?

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

Cheers

Dave

TommySzalapski

You can capture Excel instances by the window name, so I wouldn't think you'd need to write code to close other instances.

Dave

ASKER

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"

TommySzalapski

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

Dave

ASKER

> 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

TommySzalapski

The ppt charts updated with mine with the test data I gave it. Hope it works for you.

Dave

ASKER

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

Dave

ASKER

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

duup

@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...

TommySzalapski

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.

Dave

ASKER

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

TommySzalapski

Is that a question or a clarification?

Dave

ASKER

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