SetSourceData correct call parameters

Question

What is the correct call for the following sniplet

// this one throws an exception

Excel.Range range = worksheet.get_Range("A3", "B4");
myChart.SetSourceData(range.ToString(), Excel.XlRowCol.xlColumns); // Throws an 

// the thrown Exception

Exception Source:      Microsoft.Office.Interop.PowerPoint
Exception Type:        System.Runtime.InteropServices.COMException
Exception Message:     Error HRESULT E_FAIL has been returned from a 
call to a COM component.
Exception Target Site: SetSourceData

-------------------------------------------------------------

// this one doesnt compile

myChart.SetSourceData(range, Excel.XlRowCol.xlColumns);

// the compile errors

1>  error CS1502: The best overloaded method match for 'Microsoft.Office.Interop.
PowerPoint.Chart.SetSourceData(string, object)' has some invalid arguments
1>  error CS1503: Argument 1: cannot convert from 
'Microsoft.Office.Interop.Excel.Range' to 'string'

Answer 1

This is actually fairly weird, most documentation defines the first range parameter as a range object but this actually should be passed as a string

wdChart.SetSourceData("Sheet1!$A$1:$B$4", oMissing); 

Answer 2

I think this should work:

myChart.SetSourceData(dataSheet.Name + "!" + dataSheet.UsedRange.Address, Excel.XlRowCol.xlColumns);

Update: It is important to get the datasheets name. If you need to select a specific range, not the whole sheet replace dataSheet.UsedRange with your range. I needed this and tested it. It worked.

Answer 3

SetSourceData operates in completely different ways depending if you're using the method from the Excel library or another library such as PowerPoint:

Sub SetSourceData(Source As Range, [PlotBy]) Member of Excel.Chart

Sub SetSourceData(Source As String, [PlotBy]) Member of PowerPoint.Chart

In the PowerPoint model, the method is expecting a string which holds the address of the chart data range that contains the source data!

https://msdn.microsoft.com/en-us/library/office/ff746759.aspx?f=255&MSPPError=-2147217396

Same method operating in two different ways in two different MSO object models. Now that's clever Microsoft!