2

I would like to:

Create a chart in PowerPoint from VBA Excel Link the data source to a range I have in my Excel file Or replace the worksheet where PowerPoint store the data source by my data

I am a beginner in VBA (one week trying to learn)

I do this:

Dim ChartDataRange As Excel.Range
Dim myWorksheet As Worksheet
Set ChartDataRange = Excel.Range("Chart_Data_Range")
Dim ChartRelativePerfBox As PowerPoint.Shape

'Now open PowerPoint
Dim pptApp As New PowerPoint.Application

'Keep it visible because we need to see it and be able to save it
pptApp.Visible = True

'Tell VBA that FinancialSlide is a PowerPoint presentation
Dim FinancialSlide As PowerPoint.Presentation

'Add new presentation
Set FinancialSlide = pptApp.Presentations.Add

'Tell VBA that firstslide is a PowerPoint slide
Dim Slide1 As PowerPoint.Slide

'Create the first slide
Set Slide1 = FinancialSlide.Slides.Add(1,PowerPoint.PpSlideLayout.ppLayoutBlank)

Set ChartRelativePerfBox = Slide1.Shapes.AddChart( _
    Type:=xlLine, _
    Left:=Application.CentimetersToPoints(12.95), _
    Top:=Application.CentimetersToPoints(12.6), _
    Width:=Application.CentimetersToPoints(11.45), _
    Height:=Application.CentimetersToPoints(5.5))
Set myWorksheet = ChartRelativePerfBox.Chart.ChartData.Workbook.Worksheet(1)

'Replace data source in the worksheet by desired data
Application.DisplayAlerts = False
'Copy desired data
ChartDataRange.Copy
'Paste them in the chart worksheet
myWorksheet.Range("A1").PasteSpecial (xlPasteValuesAndNumberFormats)
'Delete extra columns in worksheet
myWorksheet.Columns("D:E").Select
Selection.Delete
'Resize to used range
myWorksheet.ListObjects("Table1").Resize myWorksheet.UsedRange
Application.DisplayAlerts = True

I tried to use:

Set ChartRelativePerfBox.SetSourceData( _
Source:= ChartDataRange, _
PlotBy:= xlLine)

But it doesn't work. Any guess?

My previous code works but it is so slow..

sharkbait
  • 2,980
  • 16
  • 51
  • 89
ZeLegend
  • 21
  • 4

1 Answers1

0

As a work-around to doing this in VBA, have you thought about using linked objects from excel to powerpoint?

You can create the graph in your workbook, and then copy the graph and paste it into powerpoint by doing the following:

  1. Powerpoint -> home ribbon -> clipboard.
  2. Click drop down arrow -> paste special.
  3. Click 'Paste link'.
  4. Click OK.

You will now have a chart linked to the chart in your workbook. This will update whenever you manually refresh the chart (right click -> update link) or when you open the powerpoint presentation.

Documentation can be found on the office website.

luke_t
  • 2,935
  • 4
  • 22
  • 38