title: Updating Powerpoint Graph 2010 from Excel 2010
body: <p>I want to update Powerpoint Graph 2010 from Excel 2010. 
  Code looks for the Objects and finds the range with name similar in powerpoint, it applies changes to the graph. Graph format should be same only data must be updated. </p>
  
  <p>Code is as follow, it is not able to find charts, either able to update it. </p>
  
  <pre><code>Option Explicit
  
  Private Const NAMED_RANGE_PREFIX = "Export_"
  Private Const NAMED_RANGE_PREFIX_TEXT = "ExportText"
  Private m_sLog As String
  
  Private Sub CommandButton1_Click()
  
  On Error GoTo Catch
  
  Dim pptApp As PowerPoint.Application
  Dim pptPresentation As PowerPoint.Presentation
  Dim pptSlide As PowerPoint.Slide
  Dim pptShape As PowerPoint.Shape
  
  Dim mgrChart As Chart
  Dim mgrDatasheet As Graph.DataSheet
  
  Dim rngData As Excel.Range
  
  Dim iRow As Long, iCol As Long
  Dim sTag As String
  Dim nFound As Long, nUpdated As Long
  Dim nFoundText As Long, nUpdatedText As Long
  
  Dim i As Integer
  
  Dim fLog As frmLog
  
  Dim Box1Status As VbMsgBoxResult
  
  m_sLog = ""
  
  'Prompt to Export
  Box1Status = MsgBox("Export and Save to Powerpoint Template?" &amp; Chr(13) &amp; "Reminder: Please use a clean template for export and be sure to back up the template beforehand. " &amp; Chr(13) &amp; Chr(13) &amp; "PLEASE SAVE ANY OTHER OPEN POWERPOINT DOCUMENTS AS ALL UNSAVED WORK WILL BE LOST!", vbQuestion + vbYesNo, "Confirm Export")
  If Box1Status = vbNo Then Exit Sub
  
  
  i = 1
  
  UpdateStatus "Opening Powerpoint presentation '" &amp; Range("fileloc")
  Set pptApp = New PowerPoint.Application
  pptApp.Activate
  Set pptPresentation = pptApp.Presentations.Open(Range("fileloc"))
  pptApp.WindowState = ppWindowMinimized
  
  'Looks for (tagged) charts to update
  
  UpdateStatus "Searching presentation for charts..."
  For Each pptSlide In pptPresentation.Slides
  
      For Each pptShape In pptSlide.Shapes
  
  
         If pptShape.Type = msoEmbeddedOLEObject Then
  
         If TypeOf pptShape.OLEFormat.Object Is Graph.Chart Then
  
                  nFound = nFound + 1
  
                  Set mgrChart = pptShape.OLEFormat.Object
  
                  Set mgrChart = pptShape.Chart
  
  
                  Set mgrDatasheet = mgrChart.Application.DataSheet
                  With mgrDatasheet
                      sTag = .Cells(1, 1)
                      If Left(sTag, 6) = "Export" Then UpdateStatus "Found chart on slide '" &amp; pptSlide.SlideNumber &amp; "' with tag '" &amp; sTag &amp; "'. Searching Excel workbook for same tag..."
                      Set rngData = RangeForChart(sTag)
                      If rngData Is Nothing Then
                        ' This chart has no data in this Excel workbook
                          If Left(sTag, 6) &lt;&gt; "Export" Then
                              UpdateStatus "Found chart on slide '" &amp; pptSlide.SlideNumber &amp; "' with no tag, skipping"
                          Else
                              UpdateStatus "'" &amp; sTag &amp; "' does not exist in workbook, skipping."
                          End If
                      Else
                        ' Update the PowerPoint chart with the Excel data
                          UpdateStatus "Found '" &amp; sTag &amp; "' at named range '" &amp; rngData.Name &amp; "'. Updating presentation..."
                          .Cells.ClearContents
                          For iRow = 0 To rngData.Rows.Count - 1
                              For iCol = 0 To rngData.Columns.Count - 1
                                  .Cells(iRow + 1, iCol + 1) = rngData.Cells(iRow + 1, iCol + 1)
                              Next iCol
                          Next iRow
                          .Application.Update
                          UpdateStatus "Chart with tag '" &amp; sTag &amp; "' updated."
                          nUpdated = nUpdated + 1
                      End If
                  End With
                  Set mgrDatasheet = Nothing
                  mgrChart.Application.Quit
                  Set mgrChart = Nothing
             End If
         'End If
      Next pptShape
    i = i + 1
  Next pptSlide
  
  
  UpdateStatus "Finished searching presentation. Closing PowerPoint."
  
  pptPresentation.Save
  pptPresentation.Close
  Set pptPresentation = Nothing
  pptApp.Quit
  Set pptApp = Nothing
  
  UpdateStatus "Done. " &amp; nFound &amp; " charts found and " &amp; nUpdated &amp; " charts updated. " &amp; nFoundText &amp; " text boxes found and " &amp; nUpdatedText &amp; " text boxes updated."
  
  Set fLog = New frmLog
  fLog.Caption = "Update of Powerpoint Template Complete"
  fLog.txtLog.Text = m_sLog
  fLog.Show
  Unload fLog
  Set fLog = Nothing
  Exit Sub
  
  Catch:
  MsgBox "An unexpected error occurred while updating: " &amp; Err.Number &amp; " " &amp; Err.Description, vbCritical
  ForceCleanup mgrChart, mgrDatasheet, pptPresentation, pptApp
  End Sub
  
  Private Property Get RangeForChart(sTag As String) As Range
  Dim sChartTag As String
  Dim iUpdate As Long
  Dim NameList As Range
  'Dim nRow As Range
  
  Set NameList = Range("Name_List")
  
  If Left(sTag, 6) &lt;&gt; "Export" Then Exit Property
  
  'For Each nRow In NameList.Rows
  Do While sChartTag &lt;&gt; sTag
  
      iUpdate = iUpdate + 1
    ' This will error if there is no named range for "Export_", which means that sTag does not
    ' exist in the workbook so return nothing
      On Error Resume Next
          sChartTag = ActiveWorkbook.Names(NAMED_RANGE_PREFIX &amp; NameList(iUpdate, 1).Value).RefersToRange.Cells(1, 1)
          If Err.Number &lt;&gt; 0 Then
            ' Return nothing
              Exit Property
          End If
      On Error GoTo 0
  Loop
  'Next nRow
  
  
  Set RangeForChart = ActiveWorkbook.Names(NAMED_RANGE_PREFIX &amp; NameList(iUpdate, 1).Value).RefersToRange
  
  End Property
  
  Private Property Get RangeForText(sTag As String) As Range
  Dim sTextTag As String
  Dim iUpdate As Long
  
  If Left(sTag, 10) &lt;&gt; "ExportText" Then Exit Property
  
  Do While sTextTag &lt;&gt; sTag
      iUpdate = iUpdate + 1
    ' This will error if there is no named range for "ExportText" &amp; iUpdate, which means that sTag does not
    ' exist in the workbook so return nothing
      On Error Resume Next
          sTextTag = NAMED_RANGE_PREFIX_TEXT &amp; iUpdate
          If Err.Number &lt;&gt; 0 Then
            ' Return nothing
              Exit Property
          End If
      On Error GoTo 0
  Loop
  
  Set RangeForText = ActiveWorkbook.Names(NAMED_RANGE_PREFIX_TEXT &amp; iUpdate).RefersToRange
  
  End Property
  
  Private Sub UpdateStatus(sMessage As String)
  m_sLog = m_sLog &amp; Now() &amp; ": " &amp; sMessage &amp; vbNewLine
  Application.StatusBar = Now() &amp; ": " &amp; sMessage
  DoEvents
  End Sub
  
  Private Sub ForceCleanup(mgrChart As Graph.Chart, mgrDatasheet As Graph.DataSheet, pptPresentation As PowerPoint.Presentation, pptApp As PowerPoint.Application)
  On Error Resume Next
  mgrChart.Application.Quit
  Set mgrChart = Nothing
  mgrDatasheet.Application.Quit
  Set mgrDatasheet = Nothing
  pptPresentation.Close
  Set pptPresentation = Nothing
  pptApp.Quit
  Set pptApp = Nothing
  End Sub  
  </code></pre>