Currently researching a way to use the Office XP Web Components, spreadsheet component to automate creation of both excel reporting and pivot tables to the client. I have created the pivot table via use of Excel 2002 and saved the output into the SpreadSheet XML format. I have been able to populate my data onto a separate worksheet from my pivot table. When the excel file is sent down to the client via MIME type of "application/vnd.ms-excel" the pivot table is not built correctly. I have set the following in XML as the spreadsheet object did not appear to expose this section:

<ConsolidationReference>
<FileName>Data Sheet</FileName>
<Reference>R1C1:R1000C11</Reference>
</ConsolidationReference>

Data Sheet is the spreadsheet where the data resides and the reference has the range of the data. If I go into the Pivot Table wizard and goto the second step the Range has a prepended path of where the file resides if on file or the url if opened from the web. If I remove the prepended path the rest of the statement is interpreted via the wizard as an internal spreadsheet path.
What I need is to find either a way to tell Excel not to prepend the path or to indicate that the supplied is an internal path of the file. I do not have the option to have the client run the pivot table wizard at the time of opening. It needs to be able to work without intervention or use of a macro to refresh the data via the wizard after opening as the spreadsheet xml does not expose a way to send the macro. If the user also has macros turned off that will terminate the process as unsuccessful.