OfficeXP "Cannot open pivot table source file" when refreshing pivot table

[Luc Alquier]

Client Context:
WinXP: 5.1.2600 Service Pack 1 Nu 2600
IE: 6.0.2800.1106 SP1
Excel 10.5815.4219 SP2

Server Context:
Win2000 Server+IIS

How to reproduce:

1/ From IE 6.0 Click an hyperlink (with query string part)
on an asp that returns an excel file containing a
PivotTable based on cell range.
2/ Then click on refresh from the PivotTable ToolBar.

This work fine with XL2000.
May be usefull
Asp Code:
<%@ Language=VBScript %>
<%
Response.Buffer = false
Response.ContentType = "application/vnd.ms-excel"

Const adTypeBinary = 1
Dim strFilePath
strFilePath = "D:\classeur2.xls"
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile strFilePath
Response.BinaryWrite objStream.Read
objStream.Close
Set objStream = Nothing

%>

And here is the excel file:

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-
comffice:spreadsheet"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-
comfficeffice">
<Author> </Author>
<LastAuthor> </LastAuthor>
<Created>2003-12-04T14:48:47Z</Created>
<LastSaved>2003-12-04T15:27:52Z</LastSaved>
<Company>DATASET</Company>
<Version>10.4219</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-
comfficeffice">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-
comffice:excel">
<WindowHeight>8070</WindowHeight>
<WindowWidth>13605</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>1230</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
</Style>
<Style ss:ID="s23">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
</Style>
<Style ss:ID="s24">
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
</Style>
<Style ss:ID="s25">
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
<NumberFormat/>
</Style>
<Style ss:ID="s26">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="1"
ss:Color="#000000"/>
</Borders>
<NumberFormat/>
</Style>
</Styles>
<Names>
<NamedRange ss:Name="a" ss:RefersTo="=Feuil1!R1C1:R2C2"/>
</Names>
<Worksheet ss:Name="Feuil1">
<Table ss:ExpandedColumnCount="5"
ss:ExpandedRowCount="7" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="60">
<Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="24"/>
<Column ss:AutoFitWidth="0" ss:Width="20.25"/>
<Row>
<Cell><Data ss:Type="String">a</Data><NamedCell
ss:Name="a"/></Cell>
<Cell><Data ss:Type="String">b</Data><NamedCell
ss:Name="a"/></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">1</Data><NamedCell
ss:Name="a"/></Cell>
<Cell><Data ss:Type="Number">2</Data><NamedCell
ss:Name="a"/></Cell>
</Row>
<Row ss:Index="4">
<Cell ss:Index="4" ss:StyleID="s21"><Data
ss:Type="String">Somme a</Data></Cell>
<Cell ss:StyleID="s23"/>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s21"><Data
ss:Type="String">b</Data></Cell>
<Cell ss:StyleID="s23"><Data
ss:Type="String">Somme</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s21"><Data
ss:Type="Number">2</Data></Cell>
<Cell ss:StyleID="s26"><Data
ss:Type="Number">1</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4" ss:StyleID="s24"><Data
ss:Type="String">Total</Data></Cell>
<Cell ss:StyleID="s25"><Data
ss:Type="Number">1</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-
comffice:excel">
<PageSetup>
<Header x:Margin="0.4921259845"/>
<Footer x:Margin="0.4921259845"/>
<PageMargins x:Bottom="0.984251969"
x:Left="0.78740157499999996"
x:Right="0.78740157499999996" x:Top="0.984251969"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<DisplayFormulas/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>5</ActiveRow>
<ActiveCol>4</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<PivotTable xmlns="urn:schemas-microsoft-
comffice:excel">
<Name>pt1</Name>
<HideTotalsAnnotation/>
<Location>R4C4:R7C5</Location>
<DefaultVersion>0</DefaultVersion>
<PivotField>
<Name>a</Name>
<DataType>Integer</DataType>
</PivotField>
<PivotField>
<Name>b</Name>
<Orientation>Row</Orientation>
<Position>1</Position>
<DataType>Integer</DataType>
<PivotItem>
<Name>2</Name>
</PivotItem>
</PivotField>
<PivotField>
<DataField/>
<Name>Données</Name>
<Orientation>Row</Orientation>
<Position>-1</Position>
</PivotField>
<PivotField>
<Name>Somme a</Name>
<ParentField>a</ParentField>
<Orientation>Data</Orientation>
<Position>1</Position>
</PivotField>
<PTLineItems>
<PTLineItem>
<Item>0</Item>
</PTLineItem>
<PTLineItem>
<ItemType>Grand</ItemType>
<Item>0</Item>
</PTLineItem>
</PTLineItems>
<PTSource>
<CacheIndex>1</CacheIndex>
<RefreshName> </RefreshName>
<RefreshDate>2003-12-04T16:20:41</RefreshDate>
<RefreshDateCopy>2003-12-04T16:20:41</RefreshDateCopy>
<ConsolidationReference>
<FileName>a.xml</FileName>
<Name>a</Name>
</ConsolidationReference>
</PTSource>
</PivotTable>
</Worksheet>
<Worksheet ss:Name="Feuil2">
<Table ss:ExpandedColumnCount="0"
ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="60"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-
comffice:excel">
<PageSetup>
<Header x:Margin="0.4921259845"/>
<Footer x:Margin="0.4921259845"/>
<PageMargins x:Bottom="0.984251969"
x:Left="0.78740157499999996"
x:Right="0.78740157499999996" x:Top="0.984251969"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Feuil3">
<Table ss:ExpandedColumnCount="0"
ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="60"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-
comffice:excel">
<PageSetup>
<Header x:Margin="0.4921259845"/>
<Footer x:Margin="0.4921259845"/>
<PageMargins x:Bottom="0.984251969"
x:Left="0.78740157499999996"
x:Right="0.78740157499999996" x:Top="0.984251969"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<PivotCache xmlns="urn:schemas-microsoft-
comffice:excel">
<CacheIndex>1</CacheIndex>
<Schema s:id="RowsetSchema" xmlns="uuid:BDC6E3F0-6DA3-
11d1-A2A3-00AA00C14882">
<ElementType s:name="row" s:content="eltOnly">
<attribute s:type="Col1"/>
<attribute s:type="Col2"/>
<extends s:type="rs:rowbase"/>
</ElementType>
<AttributeType s:name="Col1" rs:name="a">
<datatype dt:type="int"/>
</AttributeType>
<AttributeType s:name="Col2" rs:name="b">
<datatype dt:type="int"/>
</AttributeType>
</Schema>
<data xmlns="urn:schemas-microsoft-com:rowset">
<row Col1="1" Col2="2" xmlns="#RowsetSchema"/>
</data>
</PivotCache>
</Workbook>

 

[Wei-Dong Xu [MSFT]]

Hi Luc,

We would appreciate your patience while we are looking into this issue. We
will post our response as soon as we have any update for you.

Thanks for posting to MSDN Managed Newsgroup.

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Greg Ellison [MS]]

Hello Luc,

Thank you for your patience while we researched your issue. Using the
information in your post, I was able to reproduce the "Cannot open
PivotTable source file..." error. I reproduced the problem both in Excel
2002 and Excel 2003. The problem is caused by Excel's determining the Name
of the workbook. If you were to query the FullName property of the
workbook object after the ASP streams it down to Excel, you would see that
the FullName property contains the querystring. This is causing the
confusion for the SourceData property of the PivotCache. I will file a bug
report to Excel development so that they can study this problem in more
detail and then make a determination if this is something that can be fixed
in a future version of Excel.

I found a workaround that I believe you will find useful. Instead of using
one ASP page as in your example, we will use 2 ASP pages. The first ASP
page is the one we call and pass in a querystring. This ASP page then sets
a Session variable for each of the querystrings you pass in. Next, it
loads the second ASP page, which in turn reads the Session variables and
sets its ContentType to be handed down to Excel. This way, there is no
"querystring confusion" by Excel when it opens the second ASP page. I have
provided 2 sample ASP pages below to illustrate this workaround:

Default.asp:

<% @LANGUAGE=VBScript %>
<%
' Create a param1 session variable:
Session("param1") = Request.Querystring("ID")
' more session variables could be created here for additional querystrings
' Now open Page2.asp:
strRedirect = "Page2.asp"
Response.Write("<META HTTP-EQUIV=Refresh CONTENT=""0;URL=" + strRedirect +
""">")
%>

Page2.asp:

<% @LANGUAGE=VBScript %>
<%
sParam1 = Session("param1")
Response.ContentType = "application/x-msexcel"
Const adTypeBinary = 1
Dim strFilePath
strFilePath = "C:\test.xls"
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile strFilePath
Response.BinaryWrite objStream.Read
objStream.Close
Set objStream = Nothing
%>

From the example above, you call Default.asp passing in the querystring(s).
Then default.asp causes Page2.asp to be the one that actually opens in
Excel. Since Page2 uses Session variables for parameters, instead of
querystrings, Excel can handle the Page2.asp correctly when resolving the
SourceData location for the PivotCache.

In case some of your clients have Excel 2000, please be sure those clients
have the latest Excel 2000 updates to ensure session variables work
correctly. For example, make sure those clients have Office 2000 SR1 or
later. This is mentioned in the following article:

264143 - FIX: ASP Session Variables Empty When Office 2000 MIME Types Are
Streamed with Internet Explorer
http://support.microsoft.com/default.aspx?scid=KB;en-us;264143

Thank you for reporting this issue with Excel 2002. I hope you find the
workaround acceptable.

For information and sample code for integrating Office with Visual Basic,
Visual C++, Internet Scripts, and other programming languages, please see
http://msdn.microsoft.com/library/techart/VSOfficeDev.htm. This site
contains the most up-to-date information for using developer tools for
Office integration and extensibility.

Best regards,
Greg Ellison
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? Please visit the Microsoft Security & Privacy Center
(http://www.microsoft.com/security) for the latest news on security updates.

--------------------