Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1226
  • Last Modified:

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
0
Dave Brett
Asked:
Dave Brett
  • 9
  • 8
3 Solutions
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
Dave BrettAuthor Commented:
> 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
TommySzalapskiCommented:
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
 
Dave BrettAuthor Commented:
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
 
Dave BrettAuthor Commented:
Tommy,

Did you play any further with this?

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

Cheers

Dave
0
 
TommySzalapskiCommented:
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
 
Dave BrettAuthor Commented:
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
 
TommySzalapskiCommented:
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
 
Dave BrettAuthor Commented:
> 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
 
TommySzalapskiCommented:
The ppt charts updated with mine with the test data I gave it. Hope it works for you.
0
 
Dave BrettAuthor Commented:
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
 
Dave BrettAuthor Commented:
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
 
duupCommented:
@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
 
TommySzalapskiCommented:
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
 
Dave BrettAuthor Commented:
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
 
TommySzalapskiCommented:
Is that a question or a clarification?
0
 
Dave BrettAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now