title: Updating Powerpoint Chart's from Excel using arrays
body: <pre><code>Set PPT = GetMePowerpoint
  PPT.Visible = True
  Set PPres = PPT.presentations.Open(PPTFilePathAndName)
  With PPres
  
  For i = UBound(varTbl) To LBound(varTbl) Step -1
      Set PSlide = .slides(varSht(i))
      For j = UBound(varTbl(i)) To LBound(varTbl(i)) Step -1
          With PSlide
  
              Set pTempSlide = .Duplicate
              With pTempSlide
                  .Name = "Sheet" &amp; i &amp; "_" &amp; varSht(i) &amp; "_" &amp; j 'Give a name to slide
  
                  If varSht(i) = "Scape" Then
  
                      'check the count of columns in table for doughnut chart
                      'if columns are less than 6 then delete the extra doughnut charts
                      m = 0
                      For x = 1 To .Shapes.count
                          If .Shapes(x).Type = msoChart Then
                              m = m + 1
                          End If
                      Next x
  
                      For x = .Shapes.count To 1 Step -1
                          If .Shapes(x).Type = msoChart Then
                              If m &gt; UBound(varMktReady(i)(j), 2) - 1 Then .Shapes(x).Delete: m = m - 1
                              If m = UBound(varMktReady(i)(j), 2) - 1 Then Exit For
                          End If
                      Next x
                  End If
              End With
              Set pTempSlide = Nothing
          End With
      Next j
      Set PSlide = Nothing
  Next i
  
  ReDim tempArr(1 To 1)
  ReDim tempArr1(1 To 1)
  
  
  For i = LBound(varTbl) To UBound(varTbl)
      For j = LBound(varTbl(i)) To UBound(varTbl(i))
          Set PSlide = .slides("Sheet" &amp; i &amp; "_" &amp; varSht(i) &amp; "_" &amp; j)
          With PSlide
              .Shapes.Title.TextFrame.TextRange.Text = varTbl(i)(j)
  
              CCount = 2
              For k = 1 To .Shapes.count
                  '   determine if shape is a Chart
                  If .Shapes(k).Type = msoChart Then
                      Set pShape = PSlide.Shapes(k)
                      Set pChart = pShape.Chart
  
                      '   check if Office version is 2010
                      If Application.Version &lt;= "14.0" Then pChart.ChartData.Activate
                      Set pData = pChart.ChartData.Workbook.Worksheets(1)
                      lngPlotBy = pChart.PlotBy
  
                      '   clear source range
                      pData.Cells.Clear
  
                      'check for Doughnut chart
                      With pData
  
                          If varSht(i) = "Scape" Then
                              tempArr = varMktReady(i)(j)
                              tempArr1 = Application.Index(tempArr, 0, 1)
                              tempArr2 = Application.Index(tempArr, 0, CCount)
                              CCount = CCount + 1
  
                              '   reset sourcedata range of chart and copy table array
                              'If Application.Version &lt;= "14.0" Then
                                  pChart.SetSourceData Source:=.Range(.Cells(3, 1), .Cells(UBound(tempArr1, 1) + 2, 2)).Address(, , , xlExternal), PlotBy:=lngPlotBy
                              'Else
                              '    pChart.SetSourceData Source:=.Range(.Cells(3, 1), .Cells(UBound(tempArr1, 1) + 2, 2)), PlotBy:=lngPlotBy
                              'End If
                              .Range(.Cells(3, 1), .Cells(UBound(tempArr1, 1) + 2, 1)) = tempArr1
                              .Range(.Cells(3, 2), .Cells(UBound(tempArr1, 1) + 2, 2)) = tempArr2
                              .Parent.Close
                              pChart.Refresh
                              WaitSeconds 3
                          Else
                              '   reset sourcedata range of chart and copy table array
                              'If Application.Version &lt;= "14.0" Then
                                  pChart.SetSourceData Source:=.Range(.Cells(3, 1), .Cells(UBound(varMktReady(i)(j), 1) + 2, UBound(varMktReady(i)(j), 2))).Address(, , , xlExternal), PlotBy:=lngPlotBy
                              'Else
                              '    pChart.SetSourceData Source:=.Range(.Cells(3, 1), .Cells(UBound(varMktReady(i)(j), 1) + 2, UBound(varMktReady(i)(j), 2))), PlotBy:=lngPlotBy
                              'End If
                              .Range(.Cells(3, 1), .Cells(UBound(varMktReady(i)(j), 1) + 2, UBound(varMktReady(i)(j), 2))) = varMktReady(i)(j)
                              .Parent.Close
                              pChart.Refresh
                              WaitSeconds 3
                          End If
                      End With
                      Set pData = Nothing
                      Set pChart = Nothing
                  End If
              Next k
          End With
      Next j
  Next i
  
  '.....
  
  Public Sub WaitSeconds(intSeconds As Integer)
    On Error GoTo PROC_ERR
  
    Dim datTime As Date
  
    datTime = DateAdd("s", intSeconds, Now)
  
    Do
      Sleep 100
    Loop Until Now &gt;= datTime
  
  PROC_EXIT:
    Exit Sub
  
  PROC_ERR:
    MsgBox "Error: " &amp; Err.Number &amp; ". " &amp; Err.Description, , "modDateTime.WaitSeconds"
    Resume PROC_EXIT
  End Sub
  </code></pre>
  
  <p>i have written the above (snippet shows only powerpoint code part) code in excel which opens another workbook, from each worksheet (total 4 sheets), picks different tables into a jagged array.
  then this code opens a powerpoint template which has 4 diff chart slides and then duplicates these slides based on the table array count for the 4 different sheets. 
  once the slides are duplicated (~354 slides), the array is looped and each slide is selected and necessary table data from array is put into each Chart datasheet.</p>
  
  <p>however the problem is, the code takes a lot of time (~1 hour) to update the chart datasheets. most times excel crashes. </p>
  
  <p>When i saw task-manager, i found the reason being "automation" as everytime the chartdata was being activated and array dumped into datasheet, an external excel copy was being created. even if i tried closing the chart excel workbook, i could see multiple copies of excel process piling up. i tried WAIT, DoEvents and now SLEEP, but still most times excel hangs, unless i increase sleep to 10 secs - but that makes the chart updation too long.</p>
  
  <p>1] how do i speed up updation of chart data?
  2] is there a way to use named range in powerpoint chart workbook and then do a pastespecial paste:=values ?
  3] will a copy-paste of excel ranges (instead of using arrays) speed up powerpoint chart updation?</p>
  
  <p>any help will be most appreciated.</p>