Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default Pivot Table Filename reference in Spreadsheet XML issue

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.

  #2   Report Post  
Junior Member
 
Posts: 1
Default

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

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
OLAP Pivot table - How to show items with no data ? Timmo Excel Worksheet Functions 1 March 30th 06 07:03 PM
pivot table : formula to absolute reference a subtotal fax Excel Discussion (Misc queries) 2 November 19th 05 10:28 PM
How to get pivot table Time field to appear correctly wccmgr Excel Worksheet Functions 1 August 23rd 05 01:26 AM
Pivot table keeps dupping to another linked pivot table Angus Excel Discussion (Misc queries) 0 August 8th 05 08:48 AM
Pivot Tables, Help? Adam Excel Discussion (Misc queries) 6 March 24th 05 03:35 PM


All times are GMT +1. The time now is 09:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"