Set Source Range of charts in powerpoint

[Ram_Patel]

Hello forum,

I have a macro written in excel vba that updates the source links of charts in powerpoint to another excel file.
When running this, the source data uses the exact same range as in the previous spreadsheet (as expected).

Old Workbook

	A	B	C
1		Buyers	Sellers
2	UK	23	12
3	US	12	32
4	France	32	12
5	Germany	12	32

<tbody>
</tbody>

New Workbook

	A	B	C
1		Buyers	Sellers
2	UK	45	34
3	US	234	45
4	France	234	345
5	Germany	123	45
6	Italy	34	34
7	Spain	45	45

<tbody>
</tbody>

When updating the links, Italy and Spain would get missed out on the chart, as the source data range is exactly the same as before.

So my question is, how can I update the source data range?

Private Sub Tables()
     'Requires a reference to the Microsoft PowerPoint Library via the Tools - Reference menu in the VBE
    Dim ppApp As PowerPoint.Application
    Dim ppSlide As PowerPoint.Slide
    Dim SlideNumber As PowerPoint.SlideRange
      
'----------------------Slide 4------------------------------------
    On Error GoTo 0
    Sheets("Sheet1").Select
     
     'Look for existing instance
    On Error Resume Next
    Set ppApp = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
     
     'Make the instance visible
    ppApp.Visible = True
    
    'Copy & Paste into correct place
    ppApp.ActiveWindow.View.GotoSlide (4)
    Set ppSlide = ppApp.ActiveWindow.View.Slide
    
    lstrow = Range("C1").End(xlDown).Row
    ppSlide.Shapes("Chart 9").Chart.SetSourceData Source:=Range("A1", "C" & lstrow) 'Code fails here

end sub

The code fails and gives a "run-time error 13, Type Mismatch". This is because the source data needs to be a string rather than a range.

Any help on this would be greatly appreciated

Thanks,
Ram

 

[Ram_Patel]

Forgot to mention

I am using Office 2007 & Windows 7

Thanks,
Ram

 

[Ram_Patel]

Ok I solved this

Chart.SetSourceData Method (PowerPoint)