Updating SourceData/ Data Source of the Pivot Table

Errors: 

1. Run time error '1004' Cannot Open PivotTable source file
https://....[servlet.FileDownload?]...

2. Data source reference is not valid

3. Can not use web data source as pivot data

4. Run-time error '-2147024809 (80070057)' Item with specified name wasn't found

5. Error: Cannot open PivotTable source file ‘[filename[x].xls]SourceData’

6. Exception from HRESULT: 0x800A03EC   at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)

   at CallSite.Target(Closure , CallSite , ComObject , String )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at CallSite.Target(Closure , CallSite , Object , String )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)

Solution:

We have spend a day searching around for the solution. We get this error when we open the excel sheet in Internet Explorer. 

We see this error  "Run time error '1004' Cannot Open PivotTable source file" when 
the following statement is run in VBA. 

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

As we can observe the sheet name is appended with URI. The is incorrect. In order to restore the SourceData to original value, we have to remove the URI from the datasheet.

This is not possible until file is READ-WRITE. Thus, on workbook open we have saved the file and made it writable in Workbook_Open Event.

private void Application_WorkbookOpen(Excel.Workbook xlb){

if (Globals.ThisAddIn.Application.ActiveWorkbook.FullName.Contains("servlet.FileDownload"))
                        {

                            Application.DisplayAlerts = false;                       
    
                            String str = Globals.ThisAddIn.Application.ActiveWorkbook.FullName;
                            int index = str.IndexOf("file=");
                            String tempId = str.Substring(index);
                            xlb.SaveAs("servlet.FileDownload" + tempId);
                            Application.DisplayAlerts = true;
                        }
}

By doing this we are ensuring that the data source of the file is editable.  But it doesn't change the source of pivot tables. 

Next step is to update the SourceData. Immediately, we tried 

VSTO
((Excel.PivotTable)((Excel.Worksheet)xlb.Worksheets["Summaries"]).PivotTables("PivotTable1")).SourceData="XXXXX!A14:U51"

VBA

With Sheets("TargetSheetName").PivotTables("PivotTableName").PivotCache

.SourceData = Sheets("SourceSheetName").Range("a16:CI51").Address(True, True, xlR1C1, True)

*TargetSheetName is where pivot table resides 

We met with an exception  HRESULT: 0x800A03EC

We worked around the problem by using the following

VSTO C#

((Excel.PivotTable)((Excel.Worksheet)xlb.Worksheets["Summaries"]).PivotTables("Summary_Exp"))

.ChangePivotCache(xlb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "PivotSheetName!A14:U51"));                            

VBA

Sub Update_PTSource()
    With ActiveSheet
        .PivotTables("PivotSheetName").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="'" & .Name & "'!PTsource")
    End With
End Sub

References:

1. http://stackoverflow.com/questions/6975300/how-to-change-the-source-data-of-an-excel-pivot-table
2. http://www.mrexcel.com/forum/excel-questions/571502-macro-change-data-source-pivot-table.html