Programmatically creating a pivot table in an Excel workbook with Odata as the source

Question

Programmatically creating a pivot table in an Excel workbook with Odata as the source

I am trying to create a pivot table in an excel book using C #. The source is the URL of the Odata URL. I wrote the following code:

string connectionString = @"DATAFEED;Data Source=" + odataURL + ";Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=Basic;User ID=" + "xyz" + ";Password=" + "bvby" + ";Persist Security Info=true;Base Url=" + odataURL; Excel.PivotCache pivotCache = app.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, Type.Missing); pivotCache.Connection = connectionString;

This throws the following exception when I do pivotCache.Connection :

 System.Runtime.InteropServices.COMException was caught HResult=-2146827284 Message=Exception from HRESULT: 0x800A03EC Source="" ErrorCode=-2146827284

Stacktrace:

 at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) at Microsoft.Office.Interop.Excel.PivotCache.set_Connection(Object value) at ConsoleApplication2.Program.createWorkBookHelper(Application app, Workbook workbook, String odataURL, String nameOfWorkBook) in d:\ReportingApp\Dev\PDS\Excel Plugin\ConsoleApplication2\ConsoleApplication2\Program.cs:line 36`