Solved

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

Posted on 2010-11-22
18
1,182 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 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

11 Experts available now in Live!

Get 1:1 Help Now