none
Refreshing powerpoint charts havin excel database using VB RRS feed

  • Question

  • i am creating a Power point presentation having graphs which is having database in excel sheets.
    i inserted codes in VBA but whenever i run the code ...every time new slides appear with changes , but i want to automate my existing slides , like as any change occur in excel , ppt should b automatically refreshed.here is the coding done in VBA..



    Sub CreatePowerPoint()

    'Add a reference to the Microsoft PowerPoint Library by:
    '1. Go to Tools in the VBA menu
    '2. Click on Reference
    '3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay

    'First we declare the variables we will be using
    Dim newPowerPoint As PowerPoint.Application
    Dim activeSlide As PowerPoint.Slide
    Dim cht As Excel.ChartObject

    'Look for existing instance
    On Error Resume Next
    Set newPowerPoint = GetObject(, "PowerPoint.Application")
    On Error GoTo 0

    'Let's create a new PowerPoint
    If newPowerPoint Is Nothing Then
    Set newPowerPoint = New PowerPoint.Application
    End If
    'Make a presentation in PowerPoint
    If newPowerPoint.Presentations.Count = 0 Then
    newPowerPoint.Presentations.Add
    End If

    'Show the PowerPoint
    newPowerPoint.Visible = True

    'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
    For Each cht In ActiveSheet.ChartObjects

    'Add a new slide where we will paste the chart
    newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
    newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
    Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)

    'Copy the chart and paste it into the PowerPoint as a Metafile Picture
    cht.Select
    ActiveChart.ChartArea.Copy
    activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select

    'Set the title of the slide the same as the title of the chart
    activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text

    'Adjust the positioning of the Chart on Powerpoint Slide
    newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15
    newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 125

    activeSlide.Shapes(2).Width = 200
    activeSlide.Shapes(2).Left = 505

    Next

    AppActivate ("Microsoft PowerPoint")
    Set activeSlide = Nothing
    Set newPowerPoint = Nothing

    End Sub
    Tuesday, October 18, 2011 7:37 AM

All replies

  • Hi,

    I believe when you create a Graph/Chart in Powerpoint having Excel data in it, it will not automaticly update the Graph/Chart.

    A better approach, you could create the Graph/Chart in Excel, then link them to PowerPoint.

     

    See below article, for more info on the why:

    http://pptfaq.com/FAQ00025.htm

    This article on how to do the linking part:

    http://www.pptfaq.com/FAQ00593.htm

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Tuesday, October 18, 2011 4:25 PM
    Moderator
  • Hii,

     

    Thanks for ur reply, but i want to this linking with help of VBA.

    like we are providing a command button "update" in our excel sheet.

    what sort of code it will need so whenever we click this button instead of opening new slides , existing graphs gets refreshed

    or any article related to this.

    Wednesday, October 19, 2011 10:42 AM
  • Hii,

     

    Thanks for ur reply, but i want to this linking with help of VBA.

    like we are providing a command button "update" in our excel sheet.

    what sort of code it will need so whenever we click this button instead of opening new slides , existing graphs gets refreshed

    or any article related to this.


    When you create a chart in Excel and paste-link it into PPT, then you can let PPT do the heavy lifting and update the chart(s) itself.  You can still do this with VBA serving as the trigger for the update request to PPT.

    If you do it the way you want, there's a lot more work for you, which might be good if you / your company develops software on an hourly basis but other than that the better solution is to link the charts so that they can be updated by PPT.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Wednesday, October 19, 2011 3:25 PM
  • Check this thread out on how to update the links with VBA/Macro:

    http://skp.mvps.org/ppt00029.htm

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, October 19, 2011 9:05 PM
    Moderator
  • Hello ,

    thanks for your reply , but i want to link the database of charts in powerpoint to the present excel sheet containing Data.so that without going to chart -> edit data -> and make changes there , i want directly  open up ma excel worksheet , edit data there and whenever open powerpoint , changes are reflecting there

    Thursday, October 20, 2011 4:07 AM