manlypeeps
New Member
- Joined
- Nov 19, 2009
- Messages
- 1
Hi,
I have a Java application that Excel files to users. However, I have a problem when a users chooses to open a file that includes a pivot table without saving it locally beforehand (and so it is downloaded to the user's temp folder) as Excel thinks its data source is external - even though the pivot table is getting its data from a named range that points to another sheet in the same workbook.
I thought I had a solution with the following code that basically refreshes the pivot table's source to the correct named range:
Worksheets(sCurrentSheet).Range("A5").PivotTable.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sPivotRange, Version:=xlPivotTableVersion10)
However, this only appears to work with Excel 2007. When a user tries to open a file with Excel 2003, the error message 'Runtime error 438: Object doesn't support this method or property' is displayed. I'm guessing that it has something to with the ChangePivotCache method as I can't see it in the Excel 2003 help.
I don't want to have delete the existing pivot tables (as each file can contain multiple pivot tables), so is it possible to replace the existing pivot table cache with a new named range?
Any help is much appreciated as this is driving me insane!
Steve
I have a Java application that Excel files to users. However, I have a problem when a users chooses to open a file that includes a pivot table without saving it locally beforehand (and so it is downloaded to the user's temp folder) as Excel thinks its data source is external - even though the pivot table is getting its data from a named range that points to another sheet in the same workbook.
I thought I had a solution with the following code that basically refreshes the pivot table's source to the correct named range:
Worksheets(sCurrentSheet).Range("A5").PivotTable.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=sPivotRange, Version:=xlPivotTableVersion10)
However, this only appears to work with Excel 2007. When a user tries to open a file with Excel 2003, the error message 'Runtime error 438: Object doesn't support this method or property' is displayed. I'm guessing that it has something to with the ChangePivotCache method as I can't see it in the Excel 2003 help.
I don't want to have delete the existing pivot tables (as each file can contain multiple pivot tables), so is it possible to replace the existing pivot table cache with a new named range?
Any help is much appreciated as this is driving me insane!
Steve
