Refresh Excel Pivots using C#

See more:

C#

MS-Excel

pivot

Hi All,

What is the best way to refresh all the pivots in excel using C#.
My Pivots are linked with SQL Server and in refresh i am executing Stored procs to get data. Due to number of 10+ Pivots i do not want to delink them from SQL server. I know i can change it and keep the data in excel tab and change data source of pivots to those tabs, but believe me i cannot do that at this moment.

Currently i am using C# Interop to refresh pivots using RefreshAll method.
Below is the error i keep on getting each 3-4 days. Dont know how to handle it.

The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER)

Below is my code in C#
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = true;
excelWorkbook = excelApp.Workbooks.Open(
workbookPath,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value);

excelApp.DisplayAlerts = false;
excelApp.ScreenUpdating = false;


foreach (PivotCache cache in excelWorkbook.PivotCaches())
{
cache.BackgroundQuery = false ;
}

if (File.Exists(refreshedWorkBookPath))
{
File.Delete(refreshedWorkBookPath);
}

excelWorkbook.RefreshAll();

Please help.

Thanks,
Sajid