I have a problem that I hope someone may have an answer for.

I am programmatically creating an Excel document with a Pivot Table using
Spreadsheet XML. The data source for the Pivot table is a worksheet in the
same Excel Document as the Pivot table.

I never write this spreadsheet to disk, I simply stream the data to the
user's browser in order to launch Excel and display the spreadsheet.

The problem I am experiencing is that Excel always qualifies the reference
of the Pivot Table's data source with the file name of the Excel Document. An
abbreviated example is below.

<PTSource
<ConsolidationReference
<FileName[PivotOnline.xls]PivotDataSource</FileName
<ReferenceR1C1:R8C3</Reference
</ConsolidationReference
</PTSource

When the spreadsheet is returned to the Browser, Internet Explorer uses the
last part of the URL to name the Excel Document when it automatically
launches Excel. The name is not the name inside the spreadsheet. For example

http://www.buildrpt.com/reportcreator?parm1=1&parm2=5

I believe the Excel Document name that is assigned when IE launches Excel
for this document is "reportcreator?parm1=1&parm2=5" Since this is not the
filename, PivotOnline.xls in the pivot table reference, the pivot table data
never refreshes properly.

Does anyone know how I might get around this problem? I had two thoughts:

1) After the Excel Document is created, write it to disk with the correct
file name and send a URL back to the browser with a redirect to then load the
file with the appropriate file name. I would then have to have a clean up
process to delete the files on disk over time.
2) Write some type of Excel Macro to change the Pivot Table File name
reference to be the same as the file name given to the Excel Document when
IE launches it.

Both of these are not ideal. Is there not a way to define the FileName
reference to be more generic such as
€œ<FileName[this]PivotDataSource</FileName€�?

Any help would be greatly appreciated.

I'm experiencing the same problem. Did you ever figure out how a resolution to this issue?