Set Source Range of charts in powerpoint

Ram_Patel

New Member
Joined
Nov 8, 2013
Messages
4
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
ABC
1BuyersSellers
2UK2312
3US1232
4France3212
5Germany1232

<tbody>
</tbody>


New Workbook

ABC
1BuyersSellers
2UK4534
3US23445
4France234345
5Germany12345
6Italy 3434
7Spain4545

<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?

Code:
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
 

Forum statistics

Threads
1,081,657
Messages
5,360,291
Members
400,580
Latest member
Drexl88

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top